Project 1

Q1:-

create database ecommerce;

Q2,Q3:-

use ecommerce;
desc users_data;

Q4:-

SELECT * FROM table_name
LIMIT 100;

Q5:-

-- Count distinct values for "country" and "language"

SELECT
(SELECT COUNT(DISTINCT country) FROM users_data) AS num_distinct_countries,
(SELECT COUNT(DISTINCT language) FROM users_data) AS num_distinct_languages;

Q6:-

SELECT
gender,
SUM(CASE WHEN gender = 'M' THEN socialNbFollowers ELSE 0 END) AS male_followers_count,
SUM(CASE WHEN gender = 'F' THEN socialNbFollowers ELSE 0 END) AS female_followers_count
FROM users_data
GROUP BY gender;

Q7:-

SELECT
SUM(CASE WHEN hasProfilePicture = 'TRUE' THEN 1 ELSE 0 END) AS total_users_with_profile_picture,
SUM(CASE WHEN hasAnyApp = 'TRUE' THEN 1 ELSE 0 END) AS total_users_with_any_app,
SUM(CASE WHEN hasAndroidApp = 'TRUE' THEN 1 ELSE 0 END) AS total_users_with_android_app,
SUM(CASE WHEN hasIosApp = 'TRUE' THEN 1 ELSE 0 END) AS total_users_with_ios_app
FROM users_data;

Q8:-

SELECT country, COUNT(DISTINCT identifierHash) AS Total_buyers
FROM users_data
WHERE productsBought >= 1
GROUP BY country
ORDER BY Total_buyers DESC;

Q9:-

SELECT country, COUNT(DISTINCT identifierHash) AS Total_sellers
FROM users_data
WHERE productsSold >= 1
GROUP BY country
ORDER BY Total_sellers ASC;

Q10:-

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

Q11:-

select Language,count(hasAnyApp) Users_on_Ecom from users_data group by language;

Q12:-

SELECT 'Products Wished' AS activity, COUNT(*) AS num_activity
FROM users_data
WHERE gender = 'F' AND productsWished > 0
UNION
SELECT 'Social Products Liked' AS activity, COUNT(*) AS num_activity
FROM users_data
WHERE gender = 'F' AND socialProductsLiked > 0;

Q13:-

SELECT 'Buyers' AS activity, COUNT(*) AS num_activity
FROM users_data
WHERE gender = 'M' AND productsBought > 0
UNION
SELECT 'Sellers' AS activity, COUNT(*) AS num_activity
FROM users_data
WHERE gender = 'M' AND ProductsSold > 0;

Q14:-

SELECT country, COUNT(DISTINCT identifierHash) AS num_buyers
FROM users_data where productsBought>0
GROUP BY country
ORDER BY num_buyers DESC
LIMIT 1;

Q15:-

SELECT country
FROM users_data
WHERE productsSold = 0
GROUP BY country
LIMIT 10;

Q16:-

SELECT *
FROM users_data
WHERE daysSinceLastLogin IS NOT NULL
ORDER BY daysSinceLastLogin ASC
LIMIT 110;

Q17:-

SELECT COUNT(DISTINCT identifierHash) Female_users_not_logged_in
FROM users_data
WHERE daysSinceLastLogin>100 and gender='F';

Q18:-

select country,COUNT(DISTINCT identifierHash) Female_users_Ecom_Plat from users_data where gender='F' and hasAnyApp="TRUE" group by country;

Q19:-

select country,COUNT(DISTINCT identifierHash) Male_users_Ecom_Plat from users_data where gender='M' and hasAnyApp="TRUE" group by country;

Q20:-

select country,avg(productsSold),avg(productsBought) from users_data where gender='M' and hasAnyApp="TRUE" group by country;