Project 1

-- 1. Create new schema as ecommerce
CREATE schema ecommerce;
USE 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)
Use ecommerce;
SELECT * FROM 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 users_data LIMIT 100;

-- 5. How many distinct values exist in table for field country and language
SELECT count(DISTINCT country) country, count(DISTINCT language) language FROM users_data;

-- 6. Check whether male users are having maximum followers or female users.
SELECT gender, SUM(socialNBFollowers) FROM users_data GROUP BY gender;

-- 7. Calculate the total users those:
-- a.Uses Profile Picture in their Profile
SELECT count(hasProfilePicture) users_with_profile_picture FROM users_data WHERE hasProfilePicture ="True";
-- b. Uses Application for Ecommerce platform
SELECT count(hasAnyApp) user_with_anyapp FROM users_data WHERE hasAnyApp = "True";
-- c. Uses Android app
SELECT count(hasAndroidApp) uses_Androidapp FROM users_data WHERE hasAndroidApp = 'True';
-- d. Uses ios app
SELECT count(hasIosApp) uses_IosApp 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) buyer FROM users_data
WHERE productsBought >= 1
GROUP BY country
ORDER By buyer 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) sellers FROM users_data
WHERE productsSold >= 1
GROUP BY country
ORDER By sellers ASC;

-- 10. Display name of top 10 countries having maximum products pass rate.
SELECT country, COUNT(productsPassRate) AS PassRate FROM users_data
WHERE productsPassRate > 0
GROUP BY country
ORDER BY PassRate DESC
LIMIT 10;

-- 11. Calculate the number of users on an ecommerce platform for different language choices.
SELECT language,COUNT(language) 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 'Description', 'No. of units'
UNION SELECT 'Products Social Liked', SUM(socialProductsLiked)
FROM users_data
WHERE gender = 'F'
UNION SELECT 'Products wishlisted', SUM(productsWished) wishlisted
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 'Description', 'No. of users'
UNION SELECT 'productsSold', count(productsSold) FROM users_data
WHERE gender = 'M'
UNION SELECT 'productsBought', count(productsBought) FROM users_data
WHERE gender = 'M';

-- 14. Which country is having maximum number of buyers?
SELECT country, count(productsBought) buyers FROM users_data
WHERE productsBought > 0
GROUP By country ORDER By buyers DESC LIMIT 1;

-- 15. List the name of 10 countries having zero number of sellers
SELECT country, SUM(productsListed) AS Zerolist FROM users_data
GROUP BY country
ORDER BY Zerolist
LIMIT 10;

-- 16. Display record of top 110 users who have used ecommerce platform recently.
SELECT identifierHash, daysSinceLastLogin 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 COUNT(type) AS Femaleusers 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(gender) 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(gender) 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(productsBought) FROM users_data
WHERE gender = 'M'
GROUP BY country;