Project 1
-- 1. Create new schema as ecommerce
CREATE SCHEMA IF NOT EXISTS 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
-- 3. Run SQL command to see the structure of table
DESCRIBE users_data;
-- 4. Run SQL command to select first 100 rows of the database
SELECT * FROM users_data LIMIT 100;
-- 5. Count distinct values for country and language
SELECT COUNT(DISTINCT country) AS distinct_countries FROM users_data;
SELECT COUNT(DISTINCT language) AS distinct_languages FROM users_data;
-- 6. Check whether male users or female users have maximum followers
SELECT gender, SUM(followers) AS total_followers
FROM users_data
GROUP BY gender
ORDER BY total_followers DESC;
-- 7. Calculate the total users for different criteria
-- a. Uses Profile Picture
SELECT COUNT(*) AS total_profile_picture
FROM users_data
WHERE profile_picture IS NOT NULL;
-- b. Uses Application for Ecommerce platform
SELECT COUNT(*) AS total_ecommerce_users
FROM users_data
WHERE ecommerce_platform = 'Yes';
-- c. Uses Android app
SELECT COUNT(*) AS total_android_users
FROM users_data
WHERE app_type = 'Android';
-- d. Uses ios app
SELECT COUNT(*) AS total_ios_users
FROM users_data
WHERE app_type = 'iOS';
-- 8. Total number of buyers for each country in descending order
SELECT country, COUNT(*) AS total_buyers
FROM users_data
WHERE products_bought > 0
GROUP BY country
ORDER BY total_buyers DESC;
-- 9. Total number of sellers for each country in ascending order
SELECT country, COUNT(*) AS total_sellers
FROM users_data
WHERE products_sold > 0
GROUP BY country
ORDER BY total_sellers ASC;
-- 10. Top 10 countries having maximum products pass rate
SELECT country, AVG(product_pass_rate) AS avg_pass_rate
FROM users_data
GROUP BY country
ORDER BY avg_pass_rate DESC
LIMIT 10;
-- 11. Number of users on ecommerce platform for different language choices
SELECT language, COUNT(*) AS total_users
FROM users_data
GROUP BY language;
-- 12. Choice of female users for wishlist or social likes
SELECT 'Wishlist' AS choice, COUNT(*) AS total
FROM users_data
WHERE gender = 'Female' AND wishlist > 0
UNION
SELECT 'Social Likes', COUNT(*)
FROM users_data
WHERE gender = 'Female' AND social_likes > 0;
-- 13. Choice of male users about being seller or buyer
SELECT 'Seller' AS choice, COUNT(*) AS total
FROM users_data
WHERE gender = 'Male' AND products_sold > 0
UNION
SELECT 'Buyer', COUNT(*)
FROM users_data
WHERE gender = 'Male' AND products_bought > 0;
-- 14. Country with maximum number of buyers
SELECT country, COUNT(*) AS total_buyers
FROM users_data
WHERE products_bought > 0
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;
-- 15. List of 10 countries with zero number of sellers
SELECT country
FROM users_data
GROUP BY country
HAVING SUM(products_sold) = 0
LIMIT 10;
-- 16. Top 110 users who used ecommerce platform recently
SELECT *
FROM users_data
ORDER BY last_login DESC
LIMIT 110;
-- 17. Number of female users inactive for last 100 days
SELECT COUNT(*) AS inactive_female_users
FROM users_data
WHERE gender = 'Female' AND DATEDIFF(CURDATE(), last_login) > 100;
-- 18. Number of female users for each country on ecommerce platform
SELECT country, COUNT(*) AS female_users
FROM users_data
WHERE gender = 'Female'
GROUP BY country;
-- 19. Number of male users for each country on ecommerce platform
SELECT country, COUNT(*) AS male_users
FROM users_data
WHERE gender = 'Male'
GROUP BY country;
-- 20. Average number of products sold and bought by male users for each country
SELECT
country,
AVG(products_sold) AS avg_products_sold,
AVG(products_bought) AS avg_products_bought
FROM users_data
WHERE gender = 'Male'
GROUP BY country;