Project 1

-- Q1.Create new schema as ecommerce.

CREATE DATABASE ecommerce;

USE ecommerce;

-- Q2.Import .csv file users_data into MySQL

-- (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)

-- Its Done

-- Q3.Run SQL command to see the structure of table.

USE ecommerce;

SELECT COUNT(*) FROM users_data;

DESC users_data;

-- Q4.Run SQL command to select first 100 rows of the database.

SELECT * FROM users_data LIMIT 100;

-- Q5.How many distinct values exist in table for field country and language.

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

-- Q6.Check whether male users are having maximum followers or female users.

SELECT * FROM users_data;

SELECT gender,SUM(socialNBFollowers) followers FROM users_data GROUP BY gender;

-- Q7.Calculate the total users those,

-- 7a) Uses Profile Picture in their Profile.

SELECT COUNT(hasprofilepicture) users_with_profilepicture FROM users_data WHERE hasprofilepicture='True';

-- 7b) Uses Application for Ecommerce platform.

SELECT COUNT(hasanyapp) users_with_anyapp FROM users_data WHERE hasanyapp='True';

-- 7c) Uses Android app.

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

-- 7d) Uses ios app.

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

-- 8Q.Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers. (Hint: consider only those users having at least 1 product bought.)

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

-- Q9.Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers. (Hint: consider only those users having at least 1 product sold.)

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

-- Q10.Display name of top 10 countries having maximum products pass rate.

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

-- Q11.Calculate the number of users on an ecommerce platform for different language choices.

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

-- Q12.Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform. (Hint: use UNION to answer this question.)

SELECT 'Female choices','Count' UNION SELECT 'Liked',COUNT(socialProductsLiked) FROM users_data WHERE socialProductsLiked

UNION SELECT 'Wished',COUNT(productsWished) FROM users_data WHERE productsWished>0 AND gender='F';

-- Q13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)

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.Which country is having maximum number of buyers?.

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

-- Q15.List the name of 10 countries having zero number of sellers.

SELECT country,productsSold FROM users_data WHERE productsSold=0 limit 10;

-- Q16.Display record of top 110 users who have used ecommerce platform recently.

SELECT * FROM users_data;

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

-- Q17.Calculate the number of female users those who have not logged in since last 100 days.

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

-- Q18.Display the number of female users of each country at ecommerce platform.

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

-- Q19.Display the number of male users of each country at ecommerce platform.

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

-- Q20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.

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