Project 1

-- 1)
create database ecommerce;
use ecommerce;

-- 3) To see the structure of the users_data table
desc users_data;

-- 4)here are first 100 rows of our data

select *from users_data limit 100;

-- 5)to show distinct values for perticular field

select count(distinct country,language) from users_data;

-- 6)Check whether male users are having maximum followers or female users.
select count( case when gender='M'then 1 end ) as Male,count(case when gender='F'
then 1 end) as Female from users_data;

-- 7)
select count(*) from users_data where hasProfilePicture="True";
select count(*) from users_data where hasAnyApp="True";
select count(*) from users_data where hasAndroidApp="True";
select count(*) from users_data where hasIosApp="True";

-- 8)
select country,sum(productsBought) from users_data order by sum(productsBought) desc;

-- 9)
select country, count(productsSold) as Total_no_of_sellers from users_data where productsSold>1 group by country;

-- 10)
select country,sum(productsPassRate) from users_data group by country order by sum(productsPassRate) desc limit 10;

-- 11)
select language, count(*) from users_data group by language;

-- 12)
select sum(productsWished) as sum_of_product_wished from users_data union select sum(socialProductsLiked) as
sum_of_products_liked from users_data where gender="F";


-- 13)
select sum(productsSold) from users_data union select sum(productsBought) from users_data where gender='M';

-- 14)
select country, sum(productsBought) from users_data group by country order by sum(productsBought) desc limit 1 ;

-- 15)
select country from users_data where productsSold=0 limit 10;

-- 16)
select *from users_data order by daysSinceLastLogin asc limit 110;

-- 17)
select count(*) from users_data where daysSinceLastLogin=100 and gender="F";

-- 18)
select country,count(*) from users_data where gender="F" group by country;

-- 19)
select country,count(*) from users_data where gender="M" group by country;

-- 20)
select country,avg(productsBought),avg(productsSold) from users_data where gender="F" group by country;