Project
-- question 03
DESCRIBE ecommerce.users_data;
-- question 04
SELECT * FROM ecommerce.users_data LIMIT 100;
-- question 05
SELECT
COUNT(DISTINCT country) AS distinct_countries,
COUNT(DISTINCT language) AS distinct_languages
FROM ecommerce.users_data;
-- question 06
SELECT
CASE WHEN MAX(male_followers) > MAX(female_followers) THEN 'Male users have more followers'
WHEN MAX(male_followers) < MAX(female_followers) THEN 'Female users have more followers'
ELSE 'Male and Female users have the same number of followers'
END AS result
FROM ecommerce.users_data;
-- question 07
SELECT
COUNT(*) AS total_users_with_profile_picture,
SUM(application) AS total_users_with_application,
SUM(android_app) AS total_users_with_android_app,
SUM(ios_app) AS total_users_with_ios_app
FROM ecommerce.users_data;
-- question 08
SELECT
country,
COUNT(*) AS total_buyers
FROM ecommerce.users_data
WHERE product_bought > 0
GROUP BY country
ORDER BY total_buyers DESC;
-- question 09
SELECT
country,
COUNT(*) AS total_sellers
FROM ecommerce.users_data
WHERE product_sold > 0
GROUP BY country
ORDER BY total_sellers ASC;
-- question 10
SELECT
country,
(product_bought / product_sold) AS products_pass_rate
FROM ecommerce.users_data
ORDER BY products_pass_rate DESC
LIMIT 10;
-- question 11
SELECT
language,
COUNT(*) AS total_users
FROM ecommerce.users_data
GROUP BY language;
-- question 12
SELECT
wishlist AS choice,
COUNT(*) AS total_female_users
FROM ecommerce.users_data
WHERE gender = 'Female'
GROUP BY wishlist;
-- question 13
SELECT
CASE WHEN product_sold > 0 THEN 'Seller'
ELSE 'Buyer'
END AS choice,
COUNT(*) AS total_male_users
FROM ecommerce.users_data
WHERE gender = 'Male'
GROUP BY choice;
-- question 14
SELECT
country,
COUNT(*) AS total_buyers
FROM ecommerce.users_data
WHERE product_bought > 0
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;
-- question 15
SELECT
country
FROM ecommerce.users_data
GROUP BY country
HAVING COUNT(CASE WHEN product_sold > 0 THEN 1 END) = 0
LIMIT 10;
-- question 16
SELECT *
FROM ecommerce.users_data
ORDER BY last_login_date DESC
LIMIT 110;
-- question 17
SELECT
COUNT(*) AS female_users_not_logged_in
FROM ecommerce.users_data
WHERE gender = 'Female'
AND DATEDIFF(CURDATE(), last_login_date) > 100;
-- question 18
SELECT
country,
COUNT(*) AS total_female_users
FROM ecommerce.users_data
WHERE gender = 'Female'
GROUP BY country;
-- question 19
SELECT
country,
COUNT(*) AS total_male_users
FROM ecommerce.users_data
WHERE gender = 'Male'
GROUP BY country;
-- question 20
SELECT
country,
AVG(product_sold) AS avg_products_sold,
AVG(product_bought) AS avg_products_bought
FROM ecommerce.users_data
WHERE gender = 'Male'
GROUP BY country;