Project 1

/*
1. Create new schema as ecommerce
*/
CREATE SCHEMA ecommerce;

/*
2. Import .csv file users_data into MySQL
(right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next ->
choose options : Create a new table , select delete if exist -> next -> next)
*/
use ecommerce;
select * from users_data;

/*
3. Run SQL command to see the structure of table
*/
show tables from ecommerce;

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

/*
5. How many distinct values exist in table for field country and language
*/
select count(distinct countryCode,language) from users_data ;

/*
6. Check whether male users are having maximum followers or female users.
*/
select gender,sum(socialNbFollowers) Followers from users_data group by gender;

/*
7. Calculate the total users those
7.1 Uses Profile Picture in their Profile
7.2 Uses Application for Ecommerce platform
7.3 Uses Android app
7.4 Uses ios app
*/
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. 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(*) buyers from users_data where productsBought>0 group by countryCode order by buyers desc limit 10;

/*
9. 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(*) sellers from users_data where productsSold>0 group by countryCode order by sellers asc limit 10;

/*
10 . Display name of top 10 countries having maximum products pass rate.
*/
select country,sum(productsPassRate) TotalPassrate from users_data where productsSold>0 group by countryCode order by TotalPassrate desc limit 10;

/*
11. Calculate the number of users on an ecommerce platform for different language choices.
*/
select language,count(*) Users from users_data group by language ;

/*
12. 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 count(*) from users_data where gender='f' and productsListed>0
*/
select 'Wish Lised',count(*) Count from users_data where gender='F' and productsWished>0
union
select 'Liked in Social Platform',count(*) Count from users_data where gender='F' and socialProductsLiked>0 ;

/*
13. Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
*/
select 'Seller ',count(*) from users_data where productsSold>0
union
select 'Buyer ',count(*) from users_data where productsBought>0;

/*
14. Which country is having maximum number of buyers?
*/
select country,count(*) MaxCount from users_data where productsBought>0 group by country order by MaxCount desc limit 1;

/*
15. List the name of 10 countries having zero number of sellers.
*/
select country from users_data group by countryCode having sum(productsSold)=0 limit 10;

/*
16. Display record of top 110 users who have used ecommerce platform recently.
*/
select * from users_data order by daysSinceLastLogin asc limit 110;

/*
17. Calculate the number of female users those who have not logged in since last 100 days.
*/
select count(*) 'Not logged in last 100 days' from users_data where daysSinceLastLogin=100;

/*
18. Display the number of female users of each country at ecommerce platform.
*/
select country,count(*) 'Female Users' from users_data where gender='F' group by countryCode;

/*
19. Display the number of male users of each country at ecommerce platform.
*/
select country,count(*) 'Male Users' from users_data where gender='M' group by countryCode;

/*
20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
*/
select Country,avg(productsSold) 'Male Average Sold',avg(productsBought) 'Male Average Bought' from users_data where
gender='M' group by countryCode ;