Project 1

-- 1.
CREATE SCHEMA ecommerce;

-- 2 Data imported successfully by following the steps.alter

-- 3.
DESC users_data;

-- 4.
SELECT * FROM users_data LIMIT 100;

-- 5.
SELECT DISTINCT country FROM users_data;
SELECT COUNT(DISTINCT country), COUNT(DISTINCT language) FROM users_data ;
-- 200 distinct values for country field
-- 5 distinct values for language field

-- 6.
SELECT * FROM users_data;
SELECT gender, SUM(socialNbFollowers) followers FROM users_data GROUP BY gender; -- females are having more followers

-- 7.
-- a.
SELECT count(hasProfilePicture) profilepicture_users FROM users_data WHERE hasProfilePicture = 'TRUE';

-- 97018 users using profile pictures
-- b.
SELECT count(hasAnyApp) application_users FROM users_data WHERE hasAnyApp = 'TRUE' ;
-- 26174 users using applications
-- c.
SELECT count(hasAndroidApp) androidapp_users FROM users_data WHERE hasAndroidApp = 'TRUE' ;
-- 4819 users using android apps
-- d.
SELECT count(hasIosApp) Iosapp_users FROM users_data WHERE hasIosApp = 'TRUE' ;
-- 21527 users using ios apps

-- 8.
SELECT country,SUM(productsBought) FROM users_data WHERE productsBought > 0 GROUP BY country ORDER BY SUM(productsBought) DESC;

-- 9.
SELECT country, SUM(productsSold) FROM users_data WHERE productsSold > 0 GROUP BY country ORDER BY SUM(productsSold) ASC;

-- 10.
SELECT country FROM users_data WHERE productsPassRate>0 GROUP BY country ORDER BY MAX(productsPassRate) DESC LIMIT 10;

-- 11.
SELECT language, count(type) no_of_users FROM users_data GROUP BY language;

-- 12.
SELECT * FROM USERS_DATA;
SELECT 'products wished ', COUNT(productsWished) choices_of_females FROM users_data WHERE productsWished > 0 AND gender = 'F'
UNION
SELECT 'liked_socially', COUNT(socialProductsLiked) FROM users_data WHERE socialProductsLiked > 0 AND gender = 'F';

-- 13.
SELECT 'SELLERS', COUNT(productsSold) choices_of_males FROM users_data WHERE productsSold > 0 AND gender = 'M'
UNION
SELECT 'BUYERS', COUNT(productsBought) FROM users_data WHERE productsBought > 0 AND gender ='M';

-- 14.
SELECT country COUNTRY_OF_MAXIMUM_BUYERS, COUNT(productsBought) FROM users_data GROUP BY country ORDER BY COUNT(productsBought) DESC LIMIT 1;

-- 15.
SELECT country FROM users_data WHERE productsSold = 0 GROUP BY country ORDER BY COUNT(productsSold) DESC LIMIT 10 ;

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

-- 17.
SELECT COUNT(gender) females_not_loggedin_since_100days FROM users_data WHERE gender = 'F' AND daysSinceLastLogin >= 100;

-- 18.
SELECT country , COUNT(gender) no_of_female_users FROM users_data
WHERE gender = 'F' AND hasAnyApp = 'TRUE' GROUP BY country ORDER BY COUNT(gender) DESC;

-- 19.
SELECT country , COUNT(gender) no_of_male_users FROM users_data
WHERE gender = 'M' AND hasAnyApp = 'TRUE'
GROUP BY country ORDER BY COUNT(gender) DESC;

-- 20.
SELECT country, AVG(productsSold) sold_by_males, AVG(productsBought) bought_by_males FROM users_data
WHERE gender = 'M' AND hasAnyApp = 'TRUE' GROUP BY country;

I SUBMITTED AS BEAUTIFIED FORMAT OF SQL FILE BELOW, KINDLY CHECK that

THANK YOU