Project 1

1. Create new schema as ecommerce -

Created

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) -

imported

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

USE ecommerce;
DESC users_data;

# 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(country)) Dist_CountryCount, COUNT(DISTINCT(language)) Dist_language_count FROM users_data;

# 6. Check whether male users are having maximum followers or female users.
SELECT MAX(socialNbFollowers), gender FROM users_data GROUP BY gender;

/* 7. Calculate the total users those
a. Uses Profile Picture in their Profile
b. Uses Application for Ecommerce platform
c. Uses Android app
d. Uses ios app */
SELECT 'Count', 'Value' UNION
SELECT
'Count Profile picture', COUNT(hasProfilePicture)
FROM
users_data
WHERE
hasProfilePicture = 'True'
UNION SELECT
'Count hasAnyApp', COUNT(hasAnyApp)
FROM
users_data
WHERE
hasAnyApp = 'True'
UNION SELECT
'Count hasAndroidApp', COUNT(hasAndroidApp)
FROM
users_data
WHERE
hasAndroidApp = 'True'
UNION SELECT
'Count hasIosApp', COUNT(hasIosApp)
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(productsBought) TotalCount FROM users_data WHERE NOT 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, COUNT(productsSold) TotalCount FROM users_data WHERE NOT productsSold = 0 GROUP BY country ORDER BY COUNT(productsSold);

# 10. Display name of top 10 countries having maximum products pass rate.
SELECT country, SUM(productsPassRate) 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(type) Count 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 'F' GENDER,'Product wish' Product, SUM(productsWished) Sum FROM users_data WHERE gender ='F' UNION
SELECT 'F ','Product Like' ,SUM(socialProductsLiked) 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 'M' GENDER, 'Product seller' Product, SUM(productsSold) Sum FROM users_data UNION SELECT 'M' , 'Product buyer' ,SUM(productsBought) FROM users_data WHERE gender = 'M';

# 14. Which country is having maximum number of buyers?
SELECT country, MAX(Sum) FROM (SELECT country, SUM(productsBought) Sum FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC) temp;

# 15. List the name of 10 countries having zero number of sellers.
SELECT country, SUM(productsSold) Sum FROM users_data GROUP BY country ORDER BY SUM(productsSold) LIMIT 10;

# 16. Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM users_data ORDER BY seniority LIMIT 110;

# 17. Calculate the number of female users those who have not logged in since last 100 days.
SELECT COUNT(*) FROM users_data WHERE daysSinceLastLogin > 100 AND gender = 'F';

# 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 country;

# 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 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_productSold, AVG(productsBought) Avg_productBought FROM users_data WHERE gender = 'M' GROUP BY country;