Project 1

1. 

2.

 3. DESC users_data;
SELECT count(*) from users_data;

4. SELECT * from users_data LIMIT 100;

5. SELECT COUNT(distinct country) as country,COUNT(distinct language) as language from users_data;

6. SELECT sum(socialNbFollowers), gender
from users_data
GROUP BY gender;

We can see that the number of followers for Female gender are '2,62,458' whereas for the male gender are '77,038'.

-- Question 7a
SELECT count(hasProfilePicture) as Uses_Profile_Pic from users_data where hasProfilePicture="TRUE";

-- Question 7b
SELECT count(hasAnyApp) as Uses_Appl_for_ECommerce from users_data where hasAnyApp="TRUE";

-- Question 7c
SELECT count(hasAndroidApp) as Uses_Android_App from users_data where hasAndroidApp="TRUE";

-- Question 7d
SELECT count(hasIosApp) as Uses_IOS_App from users_data where hasIosApp="TRUE";

8. SELECT country, sum(productsBought) as Bought from users_data
WHERE productsBought>0
GROUP BY country Order by sum(productsBought) DESC;

9. SELECT country, sum(productsSold) as Sold from users_data
WHERE productsSold>0
GROUP BY country Order by sum(productsSold) ASC;

10. SELECT country, round(sum(productsPassRate)) as Pass_Rate from users_data
GROUP BY country
ORDER BY sum(productsPassRate) DESC
LIMIT 10;

11. SELECT count(identifierHash) as users, language from users_data
GROUP BY language
ORDER BY count(identifierHash) DESC;

12. SELECT 'Wishlist' AS Choice_Female, sum(productsWished) AS Products
FROM users_data
WHERE gender = 'F' AND productsWished>0
UNION
SELECT 'Social Like' AS Choice_Female, sum(socialProductsLiked) AS Products
FROM users_data
WHERE gender = 'F' AND socialProductsLiked>0;

The above data shows that majority of female as inclined to socially like the products rather than add to their wishlist.

13. SELECT 'Seller' AS Choice_Male, sum(productsSold) AS Products
FROM users_data
WHERE gender = 'M' AND productsSold>0
UNION
SELECT 'Buyer' AS Choice_Male, sum(productsBought) AS Products
FROM users_data
WHERE gender = 'M' AND productsBought>0;

In the above data it is evident that the male gender prefer to be buyers rather than sellers. 

14. SELECT country, sum(productsBought) as Total_Bought from users_data
GROUP BY country
ORDER BY sum(productsBought) DESC
LIMIT 1;

We can see that country France has the most number of buyers '3573'.

15. SELECT country, productsSold from users_data
WHERE productsSold = 0
GROUP BY country
LIMIT 10;

16. SELECT identifierHash as USER, daysSinceLastLogin as Days_Last_seen from users_data
WHERE hasAnyApp = "True" OR hasAndroidApp="True" OR hasIosApp="True"
ORDER BY daysSinceLastLogin
LIMIT 110;

17. SELECT gender, COUNT(*) as Female_not_login from users_data
WHERE gender = "F" AND daysSinceLastLogin>100;

We can see that '70189' female have not logged in since more than 100 days.

18. SELECT country, gender, COUNT(*) as Femaleusers from users_data
WHERE gender = "F"
GROUP BY country
ORDER BY Femaleusers DESC;

19. SELECT country, gender, COUNT(*) as Maleusers from users_data
WHERE gender = "M"
GROUP BY country
ORDER BY Maleusers DESC;

20. SELECT country, gender, AVG(productsSold) as Average_Sold, AVG(productsBought) as Average_Bought from users_data
WHERE gender = "M"
GROUP BY country;