Project 1

-- Tasks to be performed

-- 01
CREATE SCHEMA ecommerce;

-- 02
/* import dataset through user interface hints given in task
(right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next -> choose options :
Create a new table , select delete if exist -> next -> next) */
-- AFTER import check your file is imported or not through following commands
USE ecommerce;
SELECT * FROM users_data;
SELECT COUNT(*) FROM users_data;
-- it returns 98913 rows in table users_data

-- 03
DESC users_data;

-- 04
SELECT * FROM users_data limit 100;

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

-- 06
SELECT MAX(socialNbFollowers),SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;

-- 07
-- a
SELECT COUNT(hasProfilePicture) FROM users_data WHERE hasProfilePicture = 'TRUE';

-- b
SELECT COUNT(hasAnyApp) FROM users_data WHERE hasAnyApp ='True';

-- c
SELECT COUNT(hasAndroidApp) FROM users_data WHERE hasAndroidApp ='True';

-- d
SELECT COUNT(hasIosApp) FROM users_data WHERE hasIosApp ='True';

-- 08
SELECT country,COUNT(productsBought)Total_number_of_buyers FROM users_data WHERE productsBought >= 1
GROUP BY country
ORDER BY COUNT(productsBought)DESC;

-- 09
SELECT country,COUNT(productsSold)Total_number_of_sellers FROM users_data WHERE productsSold >=1
GROUP BY country
ORDER BY COUNT(productsSold)ASC;

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

-- 11
SELECT COUNT(type),language FROM users_data GROUP BY language;

-- 12
SELECT productsWished FROM users_data WHERE gender= 'F'
UNION
SELECT productsWished FROM users_data WHERE gender ='F';

-- or

SELECT socialProductsLiked FROM users_data WHERE gender= 'F'
UNION
SELECT socialProductsLiked FROM users_data WHERE gender ='F';

-- 13
SELECT productsBought FROM users_data WHERE gender= 'M'
UNION
SELECT productsBought FROM users_data WHERE gender ='M';

-- or
SELECT productsSold FROM users_data WHERE gender= 'M'
UNION
SELECT productsSold FROM users_data WHERE gender ='M';

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

-- IF you only one country which is highest num af buyers just do with limit

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

-- 15
SELECT max(productsSold),country FROM users_data
GROUP BY country
order by max(productsSold)ASC LIMIT 10;

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

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

-- 18
SELECT COUNT(gender)FEMALE_USERS,country FROM users_data WHERE gender='F' GROUP BY country;

-- 19
SELECT COUNT(gender)MALE_USERS,country FROM users_data WHERE gender='M' GROUP BY country;

-- 20
SELECT AVG(productsSold),AVG(productsBought),country FROM users_data WHERE gender='M' GROUP BY country;