Project 1

CREATE SCHEMA ecommerce;

use ecommerce;

describe users_data;

select * from users_data limit 100;

select count(distinct country), count(distinct language) from users_data;

select gender, sum(socialNbFollowers) as total_followers from users_data group by gender order by total_followers desc limit 1;

select count(identifierHash) from users_data where hasprofilepicture = 'True';

select count(identifierHash) from users_data where hasAnyApp = 'True';

select count(identifierHash) from users_data where hasAndroidApp = 'True';

select count(identifierHash) from users_data where hasIosApp = 'True';

select country, count(identifierHash) as buyers from users_data where productsBought > 0 group by country order by buyers desc;

select country, count(identifierHash) as sellers from users_data where productssold > 0 group by country order by sellers desc;

select country, max(productsPassRate) as maxpdpassrate from users_data group by country order by maxpdpassrate desc limit 10;

select language, count(identifierHash) as user from users_data where hasAnyApp = "True" group by language;

select identifierHash, 'liked' as choice from users_data where socialProductsLiked=1 and gender="F" and hasAnyApp="True"

union

select identifierHash, 'wishlist' as choice from users_data where productsWished=1 and gender="F" and hasAnyApp="True";

SELECT identifierHash, 'buyer' AS choice FROM users_data WHERE productsBought > 0 AND gender = "M"

UNION

SELECT identifierHash, 'seller' AS choice FROM users_data WHERE productsSold > 0 AND gender = "M";

select country, count(productsBought) FROM users_data WHERE productsBought > 0 GROUP BY country ORDER BY count(productsBought) DESC LIMIT 1;

SELECT country FROM users_data GROUP BY country having SUM(CASE WHEN productsSold > 0 THEN 1 ELSE 0 END) = 0;

SELECT * FROM users_data ORDER BY daysSinceLastLogin DESC LIMIT 110;

SELECT COUNT(*) AS female_inactive_users FROM users_data WHERE gender = 'F' AND daysSinceLastLogin > 100;

SELECT country, COUNT(*) AS total_female_users FROM users_data WHERE gender = 'F' GROUP BY country;

SELECT country, COUNT(*) AS total_male_users FROM users_data WHERE gender = 'M' GROUP BY country;

SELECT country, AVG(productsBought) AS avg_products_bought, AVG(productsSold) AS avg_products_sold

FROM users_data WHERE gender = 'M' GROUP BY country;