Project 1

-- 1. Create new schema as ecommerce
create database 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)
Successfully file imported

-- 3. Run SQL command to see the structure of table
use sallu;
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, MAX(socialNbFollowers) Max_Followers FROM users_data GROUP BY gender;

-- 7. Calculate the total users those Uses Profile Picture in their Profile, Uses Application for Ecommerce platform, Uses Android app, Uses ios app
SELECT
'Total_users', COUNT(hasProfilePicture) FROM users_data WHERE hasProfilePicture = 'TRUE'
UNION SELECT
'Total_users_Application', COUNT(hasAnyApp) FROM users_data WHERE hasAnyApp = 'TRUE'
UNION SELECT
'Total_users_Android', COUNT(hasAndroidApp) FROM users_data WHERE hasAndroidApp = 'TRUE'
UNION SELECT
'Total_users_Ios', 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) Total_Number_Of_Buyers FROM users_data
WHERE productsBought > 0 group by country order by count(productsBought) ;

-- 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) Total_Number_Of_Sellers FROM users_data
WHERE productsSold > 0 group by country order by count(productsSold) ;

-- 10. Display name of top 10 countries having maximum products pass rate.
select distinct country, max(productsPassRate) Maximum_productsPassRate from users_data
WHERE productsPassRate > 0 group by country order by max(productsPassRate) desc ;

-- 11. Calculate the number of users on an ecommerce platform for different language choices.
select count(language) from users_data;

-- 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 ' Female produts wishlist', COUNT(productsWished) choices FROM users_data
WHERE productsWished > 0 AND gender = 'F'
UNION SELECT
'Female like socially', COUNT(socialProductsLiked) choices2 FROM users_data
WHERE socialProductsLiked > 0 AND gender = 'F';

-- 13. Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
select 'Choice of Male User for Buying', count(productsBought) Choice from users_data where productsBought > 0 and gender = 'm'
union select
'Choice of Male User for Selling', count(productsSold) Choice from users_data where productsSold > 0 and gender = 'm';

-- 14. Which country is having maximum number of buyers?
SELECT country, count(productsBought) FROM users_data
group by country order by count(productsBought) desc limit 1;

-- 15. List the name of 10 countries having zero number of sellers.
SELECT country FROM users_data WHERE country=all(select productsSold=0) group by country 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 'Female', COUNT(gender) have_not_logged_in_since_last_100_days 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' and hasAnyApp = 'True' 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' and hasAnyApp = 'True' 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(productsBought) Average_of_products_Bought, avg(productsSold) Average_of_Product_Sold
from users_data where gender = 'm' and hasAnyApp = 'True' group by country;