Project 1

       3.Run SQL command to see the structure of table

           

4.Run SQL command to select first 100 rows of the database

         

5.How many distinct values exist in table for field country and language

     

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

7.Calculate the total users those

         1.Uses Profile Picture in their Profile:

         SELECT COUNT (hasAnyApp) Total_Application FROM one  WHERE hasAnyApp ="True";

1. 2. Uses Application for Ecommerce platform:
2. SELECT COUNT (hasAnyApp) Total_Application FROM one WHERE hasAnyApp ="True";
3.  
4. 3.Uses Android app:
5. SELECT COUNT(hasAndroidApp) total_use_of_android_app FROM one WHERE hasAndroidApp ="True";

       4.Uses ios app

       SELECT COUNT(hasIosApp) total_Use_of_IOS_app FROM one 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,SUM(productsBought) FROM one  GROUP BY country ORDER BY SUM(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,AVG(productsSold) FROM one GROUP BY country ORDER BY AVG(productsSold) ASC;

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

SELECT country,SUM(productsPassRate) FROM one 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(*) number_of_users FROM one  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 SUM(productsWished) Sum_of_Products_wished,SUM(socialProductsLiked) Sum_of_products_liked FROM one WHERE gender='F';

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

SELECT 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 one GROUP BY country ORDER BY SUM(productsBought) DESC;

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

SELECT productsSold,country FROM one WHERE productsSold=0 LIMIT 10;

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

SELECT * FROM one 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) number_of_femal_user_not_logged FROM one WHERE gender='F' AND daysSinceLastLogin >100;

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

select country,count(*) number_of_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(*) number_of_users from one 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 AVG(productsSold) Average_product_sold,AVG(productsBought) Average_product_bought,country FROM one WHERE gender='M' GROUP BY country;