Project 1

use ecommerce;

#Run SQL command to see the structure of table
desc users_data;
select * from users_data;

#Run SQL command to select first 100 rows of the database
select * from users_data limit 100;

#How many distinct values exist in table for field country and language
select distinct country,language from users_data;

#Check whether male users are having maximum followers or female users.
select gender,sum(socialNbfollowers) maxfollowers from users_data group by gender;

/*Calculate the total users those
Uses Profile Picture in their Profile
Uses Application for Ecommerce platform
Uses Android app
Uses ios app*/
select count(hasProfilePicture),count(hasAnyApp),count(hasAndroidApp),count(hasIosApp) from users_data
where hasProfilePicture='True' and hasAnyApp='True' and hasAndroidApp='True' and hasIosApp='True';

#Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers.
#(Hint: consider only those users having at least 1 product bought.)
select country,count(productsBought) from users_data where productsBought>0 group by country order by count(productsBought) desc;

#Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.
#(Hint: consider only those users having at least 1 product sold.)
select country,count(productsSold) from users_data where productsSold>0 group by country order by count(productsSold);

#Display name of top 10 countries having maximum products pass rate.
select country ,max(productsPassRate) from users_data group by country limit 10 ;

#Calculate the number of users on an ecommerce platform for different language choices.
select count(distinct(language)) from users_data;

#Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform.
#(Hint: use UNION to answer this question)
select gender,productsWished from users_data where gender='F'and productsWished>0
union
select gender,socialProductsLiked from users_data where gender='F' and socialProductsLiked>0;

#Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
select gender,productsBought from users_data where gender='M'
union
select gender,productsSold from users_data where gender='M';

#Which country is having maximum number of buyers?
select country,max(productsBought) from users_data ;

#List the name of 10 countries having zero number of sellers.
select country,productsSold from users_data where productsSold=0 group by country limit 10;

#Display record of top 110 users who have used ecommerce platform recently.
select * ,min(daysSinceLastLogin) from users_data group by daysSinceLastLogin limit 110;
#Calculate the number of female users those who have not logged in since last 100 days.
select count(gender='F') from users_data where daysSinceLastLogin=100;

#Display the number of female users of each country at ecommerce platform.
select country,gender from users_data where gender='F' group by country;

#Display the number of male users of each country at ecommerce platform.
select country,gender from users_data where gender='M' group by country;

#Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
select country,avg(productsSold),avg(productsBought) from users_data where gender='M' group by country;