Project 1

#Create new schema as ecommerce
CREATE SCHEMA ecommerce;
USE ecommerce;

#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)
#imported successful screenshot attached.

#Run SQL command to see the structure of table
USE ecommerce;
DESC users_data;

#Run SQL command to select first 100 rows of the database
SELECT
*
FROM
users_data
LIMIT 100;

#How many distinct values exist in table for field country and language
SELECT COUNT(DISTINCT country), COUNT(DISTINCT language)
FROM
users_data;

#Check whether male users are having maximum followers or female users.
SELECT SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;

#Calculate the total users those
#Uses Profile Picture in their Profile
#Uses Application for Ecommerce platform
#Uses Android app
#Uses ios app
SELECT
'total_users_1', COUNT(hasProfilePicture)
FROM
users_data
WHERE
hasProfilePicture = 'TRUE'
UNION SELECT
'total_users_Application', COUNT(hasAnyApp)
FROM
users_data
WHERE
hasAnyApp = 'TRUE'
UNION SELECT
'total_users_Android', COUNT(hasAndroidApp)
FROM
users_data
WHERE
hasAndroidApp = 'TRUE'
UNION SELECT
'total_users_Ios', COUNT(hasIosApp)
FROM
users_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. (Hint: consider only those users having at least 1 product bought.)
SELECT country,SUM(productsBought) FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC;

#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,SUM(productsSold) FROM users_data GROUP BY country ORDER BY SUM(productsSold) ASC;

#Display name of top 10 countries having maximum products pass rate.
SELECT country,SUM(productsPassRate) FROM users_data GROUP BY country ORDER BY SUM(productsPassRate) DESC LIMIT 10;

#Calculate the number of users on an ecommerce platform for different language choices.
SELECT language, COUNT(*) FROM users_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. (Hint: use UNION to answer this question.)
SELECT SUM(productsWished),SUM(socialProductsLiked) FROM users_data WHERE gender='F';

#Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT SUM(productsSold),SUM(productsBought) FROM users_data WHERE gender='M';

#Which country is having maximum number of buyers?
SELECT country,SUM(productsBought) FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC;

#List the name of 10 countries having zero number of sellers.
SELECT productsSold,country FROM users_data WHERE productsSold=0 LIMIT 10;

#Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

#Calculate the number of female users those who have not logged in since last 100 days.
SELECT gender,COUNT(daysSinceLastLogin) FROM users_data WHERE gender='F' AND daysSinceLastLogin >100;

#Display the number of female users of each country at ecommerce platform.
SELECT country, COUNT(*) FROM users_data WHERE gender='F' ORDER BY country;

#Display the number of male users of each country at ecommerce platform.
SELECT country, COUNT(*) FROM users_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 AVG(productsSold),AVG(productsBought),country FROM users_data WHERE gender='M' GROUP BY country;