Project 1
# Q1.
create database ecommerce;
# Q3.
Describe users_data;
# Q4.
select * from users_data limit 100;
# Q5.
select count(distinct country) from users_data;
-- Total Distinct values for the column Country ==> 200.
select count(distinct language) from users_data;
-- Total Distinct values for the column Country ==> 5.
# Q6.
select sum(socialNbFollowers) from users_data
where gender = 'M';
# Total followers for gender "M" = 77038.
select sum(socialNbFollowers) from users_data
where gender = 'F' ;
# Total followers for gender "F" = 262458.y
-- => gender 'F ' has MORE followers.
# Q7.
select count(hasProfilePicture) from users_data
where hasProfilePicture = 'True';
select count(hasAnyApp) from users_data
where hasAnyApp = 'True';
select count(hasAndroidApp) from users_data
where hasAndroidApp = 'True';
select count(hasIosApp) from users_data
where hasIosApp = 'True';
# a. Total users those who use Profile Picture in their Profile = 97018
# b. Total users those who use Uses Application for Ecommerce platform = 26174
# c. Total users who uses android app = 4819
# d. Total users who uses ios app = 21527
# Q8.
select country, sum(productsBought) 'Total Number of Buyers' from users_data
where productsBought <> 0
GROUP by country
order by sum(productsBought) desc;
# Q9.
select country, sum(productsSold) 'Total Number of Sellers' from users_data
where productsSold <> 0
GROUP by country
order by sum(productsSold);
# Q10.
select country, max(productsPassRate) from users_data
group by country
order by max(productsPassRate) desc limit 10 ;
#Q11.
select distinct language,count(type) 'Number of Users' from users_data
group by language
order by count(type) desc;
#Q12.
select gender, sum(socialProductsLiked),sum(productsWished) from users_data
where gender = 'F' and socialProductsLiked <>0 and productsWished <>0 ;
# gender 'F' has kept 118439 products in whishlist whereas they liked 305701 products.
-- Based on the above stats, if gender 'F' will like 250 products => they'll most likely put around 100 of those products in their whishlist.
#Q13.
select gender, sum(productsBought) 'Products Bought in total',sum(productsSold) 'Products sold in total' from users_data
where gender = 'M' and productsBought <>0 and productsSold <>0 ;
#gender 'M' has sold around 707 products whereas they bought around 1059 products in total.
-- Based upon that, I can say that gender 'M' most likely, are buyers rather than sellers.
#Q14.
SELECT country, MAX(productsBought) 'Number of Buyers' from users_data
where productsBought <> 0
GROUP by country
order by MAX(productsBought) desc;
# Belgique --> 405 (Maximum number of Buyers)
#Q15.
select count(distinct(country)), productsSold from users_data
where productsSold = 0 ;
#Q16.
select identifierhash 'User No.' , type ,gender, daysSinceLastLogin from users_data
order by daysSinceLastLogin limit 110;
#Q17.
select gender, count(daysSinceLastLogin) "Total Number of Users who haven't Not logged in since the last 100 days" from users_data
where gender = 'F' and daysSinceLastLogin > 100
order by daysSinceLastLogin ;
#Q18.
select Country, Gender ,count(gender) 'Number of Users' from users_data
where gender = 'F'
group by country
order by count(gender) desc;
#Q19.
select Country, Gender ,count(gender) 'Number of Users' from users_data
where gender = 'M'
group by country
order by count(gender) desc;
#Q20.
select Country, Gender , avg(productsSold) 'Average number of products sold' ,
avg(productsBought) 'Average number of products Bought' from users_data
where gender = 'M'
group by country
order by avg(productsSold) desc;