Project 1

-- Q1
-- CREATE ecommerce;

-- Q2

-- -> 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)
-- Table imported successfully uploaded
-- Q3

DESC users_data;

-- Q4

SELECT * FROM users_data LIMIT 100;

-- Q5
SELECT COUNT(DISTINCT country) Country, COUNT(DISTINCT Language) Language FROM users_data;

-- Q6

SELECT gender,SUM(socialNBFollowers)
FROM users_data GROUP BY gender;
-- Hence Female users have more followers

-- Q7
-- Uses Profile Picture in their Profile
SELECT hasProfilePicture FROM users_data;
SELECT count(type) total_users FROM users_data WHERE hasProfilePicture = 'True';
-- Uses Application for Ecommerce platform
SELECT count(type) total_users FROM users_data WHERE hasAnyApp = 'True';
-- Uses Android app
SELECT count(type) total_users FROM users_data WHERE hasAndroidApp = 'True';
-- Uses ios app
SELECT count(type) total_users FROM users_data WHERE hasIosApp = 'True';

-- Q8

SELECT country,COUNT(productsBought) Buyers FROM
users_data WHERE productsBought > 0
GROUP BY country ORDER BY Buyers DESC;

-- Q9

SELECT country,COUNT(productsSold) Sellers FROM
users_data WHERE productsSold > 0
GROUP BY country ORDER BY Sellers;

-- Q10

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

-- Q11

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

-- Q12
SELECT productsWished FROM users_data;
SELECT 'female choices','count' UNION SELECT 'liked',
COUNT(socialProductsLiked) FROM users_data
WHERE socialProductsLiked > 0 AND gender = 'F'
UNION SELECT 'wished', COUNT(productsWished)
FROM users_data WHERE productsWished > 0 AND gender = 'F';

-- Q13

SELECT 'male choices','count' UNION SELECT 'seller',
COUNT(productsSold) FROM users_data
WHERE productsSold > 0 AND gender = 'M'
UNION SELECT 'buyer',
COUNT(productsBought) FROM users_data
WHERE productsBought > 0 AND gender = 'M';

-- Q14

SELECT country, COUNT(productsBought) number_of_buyers
FROM users_data WHERE productsBought > 0
GROUP BY country ORDER BY number_of_buyers DESC;
-- France has maximum buyers


-- Q15

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

-- Q16

SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

-- Q17

SELECT gender,COUNT(type) Not_logged
FROM users_data WHERE daysSinceLastLogin >= 100
AND gender = 'F';

-- Q18

SELECT country,gender,COUNT(type) number_of_users
FROM users_data WHERE gender = 'F' GROUP BY country;

-- Q19

SELECT country,gender,COUNT(type) number_of_users
FROM users_data WHERE gender = 'M' GROUP BY country;

-- Q20

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