Project 1

1.
create database ecommerce;
2.
select * from users_data;
3.
desc users_data;
4.
select * from users_data limit 100;
5.
select count(distinct country),count(distinct language) from users_data;
6.
SELECT (gender="F",SUM(socialNbFollowers))> (gender="M",SUM(socialNbFollowers))
FROM users_data;

select gender,sum(socialNbFollowers) "socialfollowers"
from users_data group by gender;
7.a.
select count(*) "Profile pic" from users_data where hasProfilePicture = "True";
b.
select count(*) "App" from users_data where hasAnyApp="True";
c.
select count(*) "AndroidApp" from users_data where hasAndroidApp="True";
d.
select count(*) "IosApp" from users_data where hasIosApp="True";
8.
select country, sum(productsBought) "Total no of buyers for each country" from users_data where productsBought = 1
group by country order by sum(productsBought) desc;
9.
select country,sum(productsSold) "Total no of sellers for each country"
from users_data where productsSold = "1" group by country order by sum(productsSold);
10.
select country,max(productsPassRate) "maximum products pass rate" from users_data group by country order by
max(productsPassRate) desc limit 10;

select country,sum(productsPassRate) from users_data
group by country
order by sum(productsPassRate) desc limit 10;
11.
select count(*),language from users_data group by language;
12.
select sum(productsWished) "1.wishlist 2.productsliked",gender from users_data where gender = 'F' UNION
select sum(socialProductsLiked) ,gender from users_data where gender = 'F';
13.
select sum(productsSold) "1.sellers,2.buyers",gender from users_data where gender = 'M' UNION
select sum(productsBought),gender from users_data where gender = 'M' ;
14.
select country,sum(productsBought) "no of buyers" from users_data group by country order by
sum(productsBought) desc limit 10;
15.
SELECT country,productsSold FROM users_data GROUP BY country
HAVING sum(productsSold) = 0 LIMIT 1;

SELECT country,max(productsSold) FROM users_data where productsSold = 0 GROUP BY country
order by max(productsSold)=0 desc LIMIT 1;
16.
select identifierHash,daysSinceLastLogin from users_data order by daysSinceLastLogin
desc limit 110;
17.
SELECT identifierHash,gender,daysSinceLastLogin FROM users_data WHERE gender="F" AND
daysSinceLastLogin>100 ORDER BY daysSinceLastLogin;
18.
SELECT count(gender),country from users_data where gender = 'F' group by country
order by count(gender) desc;
19.
SELECT count(gender),country from users_data where gender = 'M' group by country
order by count(gender) desc;
20.
select gender,avg(productsSold) "avg product sell",avg(productsBought) "avg products buy",
country from users_data
where gender = 'M' group by country order by avg(productsSold);

select gender,avg(productsSold) "avg product sell",avg(productsBought) "avg products buy",
country from users_data
where gender = 'M' group by country order by avg(productsBought);

select gender,avg(productsSold) "avg product sell",avg(productsBought) "avg products buy",
country from users_data
where gender = 'M' group by country order by avg(productsBought),avg(productsBought);