Project 1

1.Create new schema as ecommerce:

2.Import .csv file users_data into MySQL:-

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 users_data limit 100;

5.To find distinct values exist in table for field country and language:

=>select count(distinct(country)) country,count(distinct(language)) language from users_data;

6.To Check whether male users are having maximum followers or female users:

=>select sum(socialNbFollowers) as total_followers , gender from users_data group by gender;

7. To calculate the total users those:

1. Uses Profile Picture in their Profile

=>select count(hasProfilePicture) as total_profile_picture from users_data where hasProfilePicture= 'True';

     2.Uses Application for Ecommerce platform:

=> select count(hasAnyApp) as total_Anyapplication from users_data where hasAnyApp = 'True';

     3.Uses Android app:

=>select count(hasAndroidApp) as total_android_users from users_data where hasAndroidApp ='true';

     4.Uses ios app:

=>select count(hasIosApp) as total_IosUsers from users_data where hasIosApp ='true';

8.To calculate the total number of buyers for each country and sort the result in descending order of total number of buyers:

 => select country,sum(productsBought) as total_buyers from users_data group by country order by sum(productsBought) desc;

9.To 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,avg(productsSold) from users_data group by country order by avg(productsSold) ASC;

10.Display name of top 10 countries having maximum products pass rate.

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

11.Calculate the number of users on an ecommerce platform for different language choices.

=> SELECT language, count(*) as no_of_users from users_data group by language;

12.To 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,sum(productsWished) as Total_product_wished , sum(socialProductsLiked) as Total_Social_Products_liked from users_data where gender = 'F';

13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)

=> select gender, SUM(productsSold) ,SUM(productsBought) FROM users_data WHERE gender='M';

14.Which country is having maximum number of buyers?

=> select country ,SUM(productsBought) Sum_of_product_bought FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC limit 1;

15.List the name of 10 countries having zero number of sellers.

=> select country , productsSold from users_data where productsSold=0 limit 10;

16.Display record of top 110 users who have used ecommerce platform recently.

=> SELECT * FROM users_data 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(daysSinceLastLogin) no_of_female_users from users_data
where gender = 'F' and daysSinceLastLogin>=100;

18.Display the number of female users of each country at ecommerce platform.

=>select country,count(*) no_of_female_users from users_data where gender ='F' group by country;

19.Display the number of male users of each country at ecommerce platform.

=>select country,count(*) no_of_male_users from users_data where gender ='M' group by country; 

20.To Calculate the average number of products sold and bought on ecommerce platform by male users for each country.

=>select country,avg(productsSold) as average_products_sold , avg(productsBought) as average_products_bought
from users_data where gender ='M' group by country ;