Project 1

/*Q1.Create new schema as ecommerce*/

CREATE DATABASE ecommerce;
use ecommerce;

/*#######################################################################################################################################################################*/

/*Q3.Run SQL command to see the structure of table*/
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 DISTINCT country from users_data;
SELECT DISTINCT language from users_data;

/*#######################################################################################################################################################################*/

/*Q6.Check whether male users are having maximum followers or female users.*/
SELECT COUNT(socialNbFollowers) ,gender FROM users_data GROUP BY gender;
/*FEMALES HAVE MORE FOLLOWERS COMPARE TO MALE*/

/*#######################################################################################################################################################################*/

/*
Q7.Calculate the total users those
a.Uses Profile Picture in their Profile
b.Uses Application for Ecommerce platform
c.Uses Android app
d.Uses ios app
*/
SELECT*from users_data;
select count(*)from users_data WHERE hasprofilepicture='TRUE';
select count(*) from users_data WHERE hasAnyApp='TRUE'; /*doubt*/
SELECT count(*) from users_data where hasAndroidApp='TRUE';
SELECT count(*) FROM users_data WHERE hasIosApp='TRUE';
/*Q8. 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*from users_data;
SELECT COUNT(productsbought) result,country from users_data WHERE productsBought>0 GROUP BY country order by result 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 count(productsSold) result,country from users_data WHERE productsSold>0 group by country order by result;
SELECT DISTINCT country,productsSold from users_data order by productsSold ASC ;

/*Q10.Display name of top 10 countries having maximum products pass rate.*/
SELECT*from users_data;
SELECT DISTINCT productsPassRate from users_data;
SELECT country, productSPassRate from users_data order by productsPassRate desc LIMIT 10;
/*#######################################################################################################################################################################*/

/*Q11.Calculate the number of users on an ecommerce platform for different language choices.*/
SELECT*from users_data;
SELECT distinct(language), count( *) 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*from users_data;
SELECT Count(gender) from users_data WHERE gender='F' AND productsWished>0 UNION SELECT count(gender) from users_data WHERE gender='F' and socialProductsLiked>0;

/*Q13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
(Hint: use UNION to answer this question.)*/
SELECT count(gender) from users_data WHERE gender='M' AND productsSold >0 UNION SELECT count(gender) from users_data WHERE gender='M' and productsBought>0;

/*Q14.Which country is having maximum number of buyers?*/

SELECT country ,count(productsBought) result from users_data GROUP BY country ORDER BY result desc LIMIT 1;

/*Q15.List the name of 10 countries having zero number of sellers.*/
SELECT country from users_data WHERE productsSold=0 GROUP BY country LIMIT 10;

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

/*Q17.Calculate the number of female users those who have not logged in since last 100 days.*/
SELECT count(*) from users_data WHERE gender='F' AND daysSinceLastLogin=100;

/*Q18.Display the number of female users of each country at ecommerce platform.*/
SELECT country,COUNT(*) 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(*) 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 *from users_data;

SELECT AVG(productsSold) ,AVG(productsBought) ,gender,country from users_data WHERE gender='M' AND hasAnyApp='TRUE' GROUP BY country;