Project 1

USE ecommerce;

-- ANSWER 3
DESC users_data;

-- ANSWER 4
SELECT * FROM users_data LIMIT 100;

-- ANSWER 5
SELECT COUNT(DISTINCT(country)),COUNT(DISTINCT(language)) FROM users_data;

-- ANSWER 6
SELECT SUM(socialNBFollowers) followers,gender FROM users_data GROUP BY gender;

-- ANSWER 7
-- a. Uses Profile Picture in their Profile
SELECT COUNT(hasProfilePicture) FROM users_data WHERE hasProfilePicture = 'true';
-- b. Uses Application for Ecommerce platform
SELECT COUNT(hasAnyApp) App_users FROM users_data WHERE hasAnyApp = 'true';
-- c. Uses Android app
SELECT COUNT(hasAndroidApp) Android_users FROM users_data WHERE hasAndroidApp = 'true';
-- d. Uses ios app
SELECT COUNT(hasIosApp) Ios_users FROM users_data WHERE hasIosApp = 'true';

-- ANSWER 8
SELECT country,SUM(productsBought) productsBought FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC;

-- ANSWER 9
SELECT country,SUM(productsSold) productsSold FROM users_data GROUP BY country ORDER BY SUM(productsSold);

-- ANSWER 10
SELECT country, SUM(productsPassRate) productsPassRate FROM users_data GROUP BY country ORDER BY SUM(productsPassRate) DESC LIMIT 10;

-- ANSWER 11
SELECT language, COUNT(*) users FROM users_data GROUP BY language;

-- ANSWER 12
SELECT SUM(productsWished) Womens_Coise FROM users_data WHERE gender = 'f' UNION SELECT SUM(socialProductsLiked) FROM users_data WHERE gender = 'f';

-- or without UNION

SELECT SUM(productsWished) Products_wished, SUM(socialProductsLiked) Products_liked FROM users_data WHERE gender='F';

-- ANSWER 13
SELECT SUM(productsSold) Male_Coises FROM users_data WHERE gender = 'm' UNION SELECT SUM(productsBought) FROM users_data WHERE gender = 'm';

-- or without UNION

SELECT SUM(productsSold) productsSold, SUM(productsBought) productsBought FROM users_data WHERE gender='M';

-- ANSWER 14
SELECT country, SUM(productsBought) number_of_buyers FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC;

-- ANSWER 15
SELECT country, productsSold FROM users_data WHERE productsSold = 0 LIMIT 10;

-- ANSWER 16
SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

-- ANSWER 17
SELECT gender,COUNT(daysSinceLastLogin) Females_Not_Logged FROM users_data WHERE gender='F' AND daysSinceLastLogin >100;

-- ANSWER 18
SELECT country,count(*) Female_Users FROM users_data where gender='F' group by country;

-- ANSWER 19
SELECT country,count(*) Male_Users FROM users_data where gender='M' group by country;

-- ANSWER 20
SELECT country, AVG(productsSold) Products_Sold, AVG(productsBought) Products_Bought FROM users_data WHERE gender = 'm' GROUP BY country;