Project 1

-- -- Dataset: One .csv file with name users_data with 98913 rows and 27 columns
-- ----Create new schema as ecommerce
CREATE DATABASE ecommerce;
USE ecommerce;
-- --Run SQL command to see the structure of table
DESC user_data;
-- -- Run SQL command to select first 100 rows of the database
SELECT * FROM user_data LIMIT 100;
-- -- How many distinct values exist in table for field country and language
SELECT COUNT(DISTINCT country,language) Distinct_values FROM user_data;
-- Check whether male users are having maximum followers or female users.
SELECT gender ,MAX(socialNbFollowers) maximum_follwers FROM user_data GROUP BY gender;
-- -- Calculate the total users those
-- -- Uses Profile Picture in their Profile
-- -- Uses Android app
-- -- Uses ios app
SELECT COUNT(hasprofilePicture) use_profile_pic FROM user_data WHERE hasprofilePicture ='True';
SELECT COUNT(identifierHash) No_of_Applications FROM user_data;
SELECT COUNT(hasAndroidApp)Use_AndroidApp FROM user_data WHERE hasAndroidApp ='True';
SELECT COUNT(hasIosApp)Use_IosApp FROM user_data WHERE hasIosApp ='True';
-- -- Calculate the total number of buyers for each country -
-- -- and sort the result in descending order of total number of buyers.
SELECT country ,COUNT(identifierHash) Buyers FROM user_data WHERE productsBought >=1 GROUP BY country ORDER BY country DESC;
-- --- Calculate the total number of sellers for each country--
-- -- and sort the result in ascending order of total number of sellers.
SELECT country ,COUNT(identifierHash) Sellers FROM user_data WHERE productsSold >=1 GROUP BY country ORDER BY country ASC;
-- -- Display name of top 10 countries having maximum products pass rate.
SELECT country,MAX(productsPassRate) Maximum_product_PassRate FROM user_data GROUP BY country ORDER BY MAX(productsPassRate) DESC LIMIT 10;
-- -- Calculate the number of users on an ecommerce platform for different language choices.
SELECT language ,COUNT(language) FROM user_data;
SELECT language ,COUNT(language) FROM user_data GROUP BY language;
-- - - Check the choice of female users about putting
-- -- the product in a wishlist or to like socially on an ecommerce platform.
SELECT COUNT(productsWished) productsWished FROM user_data WHERE gender='F' UNION SELECT COUNT(socialProductsLiked) socialProductsLiked FROM user_data WHERE gender='F';
-- --- Check the choice of male users about being seller or buyer.
SELECT gender,COUNT(productsSold) Sellers FROM user_data WHERE gender ='M' UNION SELECT gender,COUNT(productsBought) Buyers FROM user_data WHERE gender='M';
-- -- Which country is having maximum number of buyers
SELECT country, MAX(productsBought) maximum_number_of_buyers FROM user_data WHERE productsBought >=1;
-- --- List the name of 10 countries having zero number of sellers.
SELECT country ,productsSold FROM user_data WHERE productsSold=0 LIMIT 10;
-- - -Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM user_data ORDER BY daysSinceLastLogin ASC LIMIT 110 ;
-- -- -- Calculate the number of female users those who have not logged in since last 100 days.
SELECT gender ,COUNT(gender) Not_login FROM user_data WHERE gender ='F' AND daysSinceLastLogin NOT BETWEEN 1 AND 100 ORDER BY daysSinceLastLogin ASC ;
-- -- Display the number of female users of each country at ecommerce platform.
SELECT country ,COUNT(gender) No_female_users FROM user_data WHERE gender ='F' GROUP BY country;
-- -- -- Display the number of male users of each country at ecommerce platform.
SELECT country, COUNT(gender) NO_male_users FROM user_data WHERE gender ='M' GROUP BY country;
-- -- Calculate the average number of products sold and bought
-- -- -- on ecommerce platform by male users for each country.
SELECT country,productsSold,productsBought,AVG(productsSold) AVG_OF_productsSold ,AVG(productsBought)AVG_OF_productsBought ,gender FROM user_data WHERE gender='M' GROUP BY country;