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)
select count(*) from ecommerce.users_data;

# 3. Run SQL command to see the structure of table
desc users_data;

# 4. Run SQL command to select first 100 rows of the database
SELECT * FROM ecommerce.users_data Limit 100;

# 5. How many distinct values exist in table for field country and language
SELECT count(distinct(country)) as 'country',count(distinct(language)) as 'language' FROM ecommerce.users_data ;

# 6. Check whether male users are having maximum followers or female users.
SELECT gender 'Users', sum(socialNbFollowers) as 'Maximum Followers' FROM ecommerce.users_data Group By gender ;

# 7. Calculate the total users those
# a. Uses Profile Picture in their Profile
select count(hasProfilePicture) 'Uses Profile Picture in their Profile' FROM ecommerce.users_data where hasProfilePicture ='True';

# b. Uses Application for Ecommerce platform
select count(hasAnyApp) 'Uses Application for Ecommerce platform' FROM ecommerce.users_data where hasAnyApp ='True';

# c. Uses Android app
SELECT count(hasAndroidApp) 'Uses Android app' FROM ecommerce.users_data Where hasAndroidApp ='true' ;

# d. Uses ios app
SELECT count(hasIosApp) 'Uses ios app' FROM ecommerce.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 as Country, count(productsBought) 'Total No of Buyers' from ecommerce.users_data where productsBought > 0 group by country order by count(productsBought) desc;

# 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 as Country, count(productsSold) 'Total No of Sellers' from ecommerce.users_data where productsSold > 0 group by country order by count(productsSold) asc;

# 10. Display name of top 10 countries having maximum products pass rate.
Select country 'Country',max(productsPassRate) 'Maximum Products Pass Rate' from ecommerce.users_data group by country having max(productsPassRate) limit 10 ;

# 11. Calculate the number of users on an ecommerce platform for different language choices.
Select language 'Languages', count(language) '' from ecommerce.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 'productsWished' as 'Type', Count(productsWished) 'product in a wishlist or to like socially' FROM ecommerce.users_data Where gender = 'F' and productsWished>0
union
select 'socialProductsLiked', count(socialProductsLiked) FROM ecommerce.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' as 'Type', count(productsSold) 'Count being seller or buyer' FROM ecommerce.users_data Where gender = 'M' and productsSold > 0
union
select 'Buyer', count(productsBought) FROM ecommerce.users_data Where gender = 'M' and productsBought > 0;

# 14. Which country is having maximum number of buyers?
select country,max(productsBought) 'Max no of buyers' from ecommerce.users_data group by country order by max(productsBought) desc limit 1;

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

# 16. Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM ecommerce.users_data where daysSinceLastLogin >0 order by daysSinceLastLogin Limit 110 ;

# 17. Calculate the number of female users those who have not logged in since last 100 days.
SELECT gender, count(gender) FROM ecommerce.users_data where gender ='F' and daysSinceLastLogin < 100 ;

# 18. Display the number of female users of each country at ecommerce platform.
SELECT country, count(type) FROM ecommerce.users_data where gender ='F' group by country;

# 19. Display the number of male users of each country at ecommerce platform.
SELECT country, count(type) FROM ecommerce.users_data where gender ='M' group by country;

# 20. 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 ecommerce.users_data where gender ='M' and productsBought > 0 or productsSold > 0 group by country;