Project 1

use ecommerce;
select count(*)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)) FROM users_data;
SELECT DISTINCT(country) FROM users_data;

SELECT DISTINCT(language) FROM users_data;
SELECT COUNT(DISTINCT(language)) FROM users_data;

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

SELECT SUM(socialNbFollowers) FROM users_data WHERE gender= 'M';
SELECT SUM(socialNbFollowers) FROM users_data WHERE gender= 'F';

-- 7.Calculate the total users those
-- Uses Profile Picture in their Profile
-- Uses Application for Ecommerce platform
-- Uses Android app
-- Uses ios app

SELECT * FROM users_data LIMIT 10;
SELECT COUNT(hasProfilePicture) FROM users_data WHERE hasProfilePicture='True';
SELECT COUNT(hasAnyApp) FROM users_data WHERE hasProfilePicture='True';
SELECT COUNT(hasAndroidApp) FROM users_data WHERE hasAndroidApp='True';
SELECT 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.

SELECT * FROM users_data LIMIT 10;
SELECT COUNT(productsBought),country FROM users_data GROUP BY country ORDER BY COUNT(productsBought) DESC ;
-- SELECT gender,SUM(productsBought) OVER(PARTITION BY gender) as sumo FROM users_data;

-- 9.Calculate the average number of sellers for each country and sort the result in ascending order of total number of sellers.
SELECT * FROM users_data LIMIT 10;
SELECT AVG(productsSold) total_number_of_sellers,country FROM users_data GROUP BY country ORDER BY total_number_of_sellers ASC;

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

SELECT * FROM users_data LIMIT 10;

SELECT MAX(productsPassRate),country FROM users_data GROUP BY country ORDER BY MAX(productsPassRate) DESC LIMIT 10;

-- 11.Calculate the number of users on an ecommerce platform for different language choices.
SELECT * FROM users_data LIMIT 10;
SELECT DISTINCT(type) FROM users_data;
SELECT COUNT(type),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.
SELECT * FROM users_data LIMIT 10;

SELECT COUNT(socialProductsLiked), COUNT(productsWished),gender FROM users_data WHERE gender= 'F';

-- 13.Check the choice of male users about being seller or buyer.
SELECT * FROM users_data LIMIT 10;
SELECT COUNT(productsSold), COUNT(productsBought),gender FROM users_data GROUP BY gender='M';

-- 14. Which country is having maximum number of buyers?

SELECT * FROM users_data LIMIT 10;
SELECT MAX(productsBought),country FROM users_data GROUP BY country;

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

SELECT * FROM users_data LIMIT 10;
SELECT MIN(productsSold),country FROM users_data GROUP BY country ORDER BY MIN(productsSold) LIMIT 10;

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

SELECT * FROM users_data LIMIT 10;
SELECT * FROM users_data ORDER BY daysSinceLastLogin ASC LIMIT 110;

-- 17.Calculate the number of female users those who have not logged in since last 100 days.

SELECT COUNT(gender) FROM users_data WHERE gender='F' AND daysSinceLastLogin >=100;
-- SELECT COUNT(type) FROM users_data WHERE gender='F' AND daysSinceLastLogin >=100;

-- 18.Display the number of female users of each country at ecommerce platform.
SELECT * FROM users_data LIMIT 10;
SELECT COUNT(type),gender,country FROM users_data WHERE gender= 'F' GROUP BY country ORDER BY COUNT(gender) DESC;
SELECT COUNT(gender),gender,country FROM users_data WHERE gender= 'F' GROUP BY country ORDER BY COUNT(gender) DESC;

-- 19.Display the number of male users of each country at ecommerce platform.
SELECT COUNT(type),gender,country FROM users_data WHERE gender= 'M' GROUP BY country ORDER BY COUNT(gender) DESC;
SELECT COUNT(gender),gender,country FROM users_data WHERE gender= 'M' GROUP BY country ORDER BY COUNT(gender) DESC;

-- 20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT * FROM users_data LIMIT 10;
SELECT gender,AVG(productsSold), AVG(productsBought),country FROM users_data WHERE gender='M' GROUP BY country;