Project 1

use ecommerce;

create schema ecommerce;

create table users_data;

desc datanew;

select * from data;

LOAD DATA INFILE 'C:\Program Files\MySQL\MySQL Server 8.0\Uploads\users_data.csv'

INTO TABLE users_data

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

SHOW VARIABLES LIKE 'secure_file_priv';

SELECT COUNT(*) AS total_rows FROM datanew;

SELECT COUNT(DISTINCT country) AS unique_countries,

COUNT(DISTINCT language) AS unique_languages

FROM datanew;

select gender, sum(socialNbFollowers) as followers from datanew group by gender;

SELECT

COUNT(CASE WHEN hasProfilePicture ='True' THEN 1 END) AS users_with_profile_picture,

COUNT(CASE WHEN hasAnyApp = 'True' THEN 1 END) AS app_users,

COUNT(CASE WHEN hasAndroidApp = 'True' THEN 1 END) AS android_users,

COUNT(CASE WHEN hasIosApp = 'True' THEN 1 END) AS ios_users

FROM datanew;

select country, count(productsBought) as buyers from datanew where productsBought > 0

group by country

order by buyers desc;

select country, count(productssold) as sellers from datanew where productssold > 0

group by country

order by Sellers desc;

select country, sum(productspassrate ) as Passrate from datanew group by country order by Passrate desc limit 10 ;

SELECT language, count(hasanyapp) AS total_users_with_app

FROM datanew where hasanyapp like 'True' GROUP BY language;

SELECT 'Wishlist' AS preference, sum(productsWished) AS total

FROM datanew WHERE gender = 'F' AND productsWished > 0

UNION

SELECT 'Social Likes' AS preference, sum(socialProductsLiked) AS total

FROM datanew WHERE gender = 'F' AND socialProductsLiked > 0;

select count(productsBought) as buyer from datanew where gender='M' and productsBought >0

union select count(productsSold) as seller from datanew where gender='M'and productssold > 0;

select country, count(productsbought) as buyer from datanew group by country order by buyer desc limit 1;

select country, sum(productssold) as seller from datanew where productssold=0 group by country order by seller

asc limit 10;

select identifierhash from datanew order by daysSinceLastLogin asc limit 110;

select count(gender) as Female_User from datanew where gender='F'and dayssincelastlogin<99;

SELECT country,

sum(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_users,

sum(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_users

FROM datanew

GROUP BY country

ORDER BY male_users DESC, female_users desc;

SELECT country,

AVG(productssold) AS avg_sold,

AVG(productsbought) AS avg_bought

FROM datanew

WHERE gender = 'M'

GROUP BY country;