Project 1

-- Ans1--
CREATE DATABASE ecommerce;

-- Ans2--
-- Imported data from csv file.

-- Ans3--
USE ecommerce;
SELECT * FROM users_data;

-- Ans4--
SELECT * FROM users_data LIMIT 100;

-- Ans5--
SELECT COUNT(DISTINCT(country)),COUNT(DISTINCT(language)) FROM users_data;
-- 200 distict values of country and 5 distinct values of language exists.

-- Ans6--
SELECT gender, SUM(socialNbFollowers) FROM users_data GROUP BY gender;
-- Female users are having maximum followers.

-- Ans7--
SELECT COUNT(type) total_users FROM users_data WHERE hasProfilePicture='True';
SELECT COUNT(type) total_users FROM users_data WHERE hasAnyApp='True';
SELECT COUNT(type) total_users FROM users_data WHERE hasAndroidApp='True';
SELECT COUNT(type) total_users FROM users_data WHERE hasIosApp='True';

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

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

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

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

-- Ans12--
SELECT 'female choices', 'Count' From users_data 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';

-- Ans13--
SELECT 'Males' , 'Count' from users_data 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';

-- Ans14--
SELECT country,COUNT(productsBought) MaxNoOfBuyers from users_data GROUP BY country ORDER BY MaxNoOfBuyers DESC LIMIT 1;
-- France is having maximum buyers 22135--

-- Ans15--
SELECT country, COUNT(productsSold) FROM users_data WHERE productsSold=0 GROUP BY country LIMIT 10;

-- Ans16--
SELECT identifierHash,country, gender,daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin ASC LIMIT 110;

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

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

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

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