Project 1
USE ecommerce;
select * from users_data;
desc users_data;
select * from users_data
limit 100;
select distinct country, language from users_data;
SELECT
SUM(socialNbFollowers) AS total_followers, gender
FROM
users_data
GROUP BY gender;
with followers_info as (
select gender, sum(socialNbFollowers) as total_followers
from user_data
group by gender
)
select count(hasProfilePicture) as Total_profle_picture from users_data where hasProfilePicture = 'True';
select count(hasAnyApp) as Total_User_application from users_data where hasAnyApp = 'True';
select count(hasAndroidApp) as Total_androidApp from users_data where hasAndroidApp = 'True';
select count(hasIosApp) as Total_androidApp from users_data where hasIosApp = 'True';
select country, count(country) as product_Bought from users_data where productsBought > 0 group by country order by country desc ;
select country, count(country) as product_sold from users_data where productsSold > 0 group by country order by country asc ;
select country, count(country) as product_PassRate from users_data where productsPassRate > 0 group by country order by product_passRate desc limit 10;
select language, count(language) as Number_of_users from users_data group by language;
SELECT choice,
COUNT(*) as Number_of_female_Users
FROM(
select 'Wishlist' as choice from users_data
where GENDER = 'F' AND PRODUCTSWISHED>0
UNION All
SELECT 'Social Like' as choice from users_data
WHERE GENDER = 'F' AND SOCIALPRODUCTSLIKED>0
) as subquerry group by choice ;
SELECT choice,
COUNT(*) as Number_of_Male_Users
FROM(
select 'Product Sold' as choice from users_data
where GENDER = 'M' AND PRODUCTSSOLD>0
UNION All
SELECT 'Products Bought' as choice from users_data
WHERE GENDER = 'F' AND PRODUCTSBOUGHT>0
) as subquerry group by choice ;
SELECT COUNT(PRODUCTSBOUGHT) as Product_brought, Country
FROM users_data
where PRODUCTSBOUGHT > 0
GROUP BY Country;
SELECT COUNT(PRODUCTSsold) as Zero_sold_Users, Country
FROM users_data
where PRODUCTSsold = 0
GROUP BY Country
limit 10;
select * from users_data order by daysSinceLastlogin
limit 110;
SELECT COUNT(daysSinceLastlogin) as Not_logging_since_100days, gender
FROM users_data
where daysSinceLastlogin > 100 and gender = 'F'
GROUP BY Gender;
select count(gender) as Number_of_Female_user, country
from users_data
where gender ='F'
group by country;
select count(gender) as Number_of_Female_user, country
from users_data
where gender ='M'
group by country;
select avg(productsSold) as Average_productsold, avg(productsBought) as Average_productBought, country
from users_data
where gender ='M'
group by country;