Project 1

#Project 1
#Create new schema as ecommerce
CREATE DATABASE ecommerce ;

#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) distinct_country , COUNT(DISTINCT language) distinct_language FROM users_data;

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

#Calculate the total users those
#a) Uses Profile Picture in their Profile
SELECT COUNT(*) total_users_with_profile_picture FROM users_data WHERE hasProfilePicture='True';
#b)Uses Application for Ecommerce platform
SELECT COUNT(*) total_users FROM users_data WHERE hasAnyApp='True';
#c)Uses Android app
SELECT COUNT(*) total_users FROM users_data WHERE hasAndroidApp='True';
#d) Uses ios app
SELECT COUNT(*) total_users FROM users_data WHERE hasIosApp='True';

#To calculate the total number of buyers for each country and
#sort the result in descending order of total number of buyers
SELECT country ,SUM(productsBought) Total_buyers 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 ,AVG(productsSold) Total_sellers FROM users_data WHERE productsSold>0 GROUP BY country ORDER BY
AVG(productsSold) ASC;

#Display name of top 10 countries having maximum products pass rate.
SELECT country,sum(productsPassRate) Products_Pass_Rate 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 COUNT(language) , language 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.
SELECT gender ,SUM(socialProductsLiked) Products_Liked ,SUM(productsWished) Products_Wished
FROM users_data WHERE gender = 'F';

#Check the choice of male users about being seller or buyer.
SELECT gender ,SUM(productsBought) Buyers , SUM(productsSold) Sellers FROM users_data WHERE gender ='M' ;

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

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

#Display record of top 110 users who have used ecommerce platform recently.
SELECT identifierHash,gender, daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin asc limit 110;

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

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

#Display the number of male users of each country at ecommerce platform.
SELECT gender,country,COUNT(*) No_of_Male_Users FROM users_data where gender='M'
GROUP BY country ORDER BY country ASC;

#Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT country , AVG(productsSold) Products_Sold , AVG(productsBought) Products_Bought FROM users_data WHERE gender ='M'
GROUP BY country ORDER BY country ;