Project 1
-- Q.1
CREATE DATABASE ecommerce;
USE ecommerce;
-- Q.3
DESCRIBE users_data;
-- Q.4
SELECT * FROM users_data LIMIT 100;
-- Q.5
SELECT DISTINCT country FROM users_data;
SELECT DISTINCT language FROM users_data;
-- Q.6
SELECT gender, SUM(socialNbFollowers) Total_Followers
FROM users_data
GROUP BY gender
ORDER BY Total_Followers
LIMIT 1;
-- Q.7
SELECT COUNT(*) Total_users_using_profile_picture FROM users_data
WHERE hasProfilePicture = 'TRUE';
SELECT COUNT(*) Total_users_using_Any_Apps FROM users_data
WHERE hasAnyApp = 'TRUE';
SELECT COUNT(*) Total_users_using_Android_App FROM users_data
WHERE hasAndroidApp = 'TRUE';
SELECT COUNT(*) Total_users_using_IOS_App FROM users_data
WHERE hasIosApp = 'TRUE';
-- Q.8
SELECT country, COUNT(*) Total_Buyers FROM users_data
WHERE productsBought > 1
GROUP BY country
ORDER BY Total_Buyers DESC;
-- Q.9
SELECT country, COUNT(*) Total_Sellers FROM users_data
WHERE productsSold > 1
GROUP BY country
ORDER BY Total_Sellers;
-- Q.10
SELECT country, COUNT(*) Product_Pass_Rate FROM users_data
WHERE productsPassRate > 0
GROUP BY country
ORDER BY Product_Pass_Rate DESC
LIMIT 10;
-- Q.11
SELECT language, COUNT(*) total_users FROM users_data
GROUP BY language;
-- Q.12
SELECT gender, COUNT(*) total_wishlist
FROM users_data
WHERE gender = 'F' AND productsWished = 1
UNION
SELECT gender, COUNT(*) total_likes
FROM users_data
WHERE gender = 'F' AND socialProductsLiked = 1;
-- Q.13
SELECT gender, COUNT(*) total_sellers
FROM users_data
WHERE gender = 'M' AND productsSold > 0
UNION
SELECT gender, COUNT(*) total_buyers
FROM users_data
WHERE gender = 'M' AND productsBought > 0;
-- Q.14
SELECT country, COUNT(*) total_buyers
FROM users_data
WHERE productsBought > 0
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;
-- Q.15
SELECT country
FROM users_data
GROUP BY country
HAVING SUM(productsSold) = 0
LIMIT 10;
-- Q.16
SELECT * FROM users_data
ORDER BY daysSinceLastLogin
LIMIT 110;
-- Q.17
SELECT COUNT(*) total_inactive_females
FROM users_data
WHERE gender = 'F' AND daysSinceLastLogin > 100;
-- Q.18
SELECT country, COUNT(*) total_females
FROM users_data
WHERE gender = 'F'
GROUP BY country;
-- Q.19
SELECT country, COUNT(*) total_females
FROM users_data
WHERE gender = 'M'
GROUP BY country;
-- Q.20
SELECT country, AVG(productsSold) AS avg_products_sold, AVG(productsBought) AS avg_products_bought
FROM users_data
WHERE gender = 'M'
GROUP BY country;