Project 1

#1.Create new schema as ecommerce*/
create database ecommerce;

/* Q-2.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)

*/
use ecommerce;
select * from users_data;

/*
3.Run SQL command to see the structure of table
*/
USE ecommerce;
select * from users_data;
DESC users_data;

/*
4.Run SQL command to select first 100 rows of the database
*/
USE ecommerce;
select * from users_Data limit 100;

/*
5.How many distinct values exist in table for field country and language
*/
USE ecommerce;
select count(distinct(country)),count(distinct(language)) from users_data;

/*
6.Check whether male users are having maximum followers or female users.
*/
USE ecommerce;
SELECT SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;
#here female user having 3032 followers are mamimum male having 826 followers


/*
7.Calculate the total users those
a.Uses Profile Picture in their Profile
*/
USE ecommerce;
select count(hasProfilePicture) Total_Profile_Picture from users_Data where hasProfilePicture='True';

/*
b.Uses Application for Ecommerce platform
*/
USE ecommerce;
select count(hasAnyApp) Total_Application FROM users_data WHERE hasAnyApp ="True";

/*c.Uses Android app*/
USE ecommerce;
SELECT COUNT(hasAndroidApp) total_use_of_android_app FROM users_data WHERE hasAndroidApp ="True";

/*d.Uses ios app*/
USE ecommerce;
SELECT COUNT(hasIosApp) total_Use_of_IOS_app FROM users_data WHERE hasIosApp ="True";


/*
8.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.)
*/
USE ecommerce;
select * from users_data;

SELECT COUNTRY,COUNT(productsBought) FROM users_data WHERE productsBought > 0 group by country order by count(productsBought) DESC;

/*
9.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.)
*/
USE ecommerce;

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

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

/*
11.Calculate the number of users on an ecommerce platform for different language choices.
*/
USE ecommerce;
SELECT language, COUNT(*) number_of_users FROM users_data GROUP BY language;

/*
12.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.)
*/
USE ecommerce;

SELECT ' put produts in wishlist',count(productsWished) total_product FROM users_data WHERE productsWished > 0 AND gender = 'F'
UNION SELECT 'like socially', count(socialProductsLiked) FROM users_data WHERE socialProductsLiked > 0 AND gender = 'F';

/*
13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
*/
USE ecommerce;

SELECT'selling', count(productsSold) choices FROM users_data WHERE productsSold > 0 AND gender = 'M'
UNION SELECT 'buying', count(productsBought) choices2 FROM users_data WHERE productsBought > 0 AND gender = 'M';

/*
14.Which country is having maximum number of buyers?
*/
USE ecommerce;
select country ,SUM(productsBought) Sum_of_product_bought FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC limit 1;

/*15. List the name of 10 countries having zero number of sellers. */
USE ecommerce;
SELECT productsSold,country FROM users_data WHERE productsSold=0 LIMIT 10;

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

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

/*
18.Display the number of female users of each country at ecommerce platform.
*/
USE ecommerce;
select country,count(gender) number_of_female_users from users_data where gender='F' group by country ;

/*19.Display the number of male users of each country at ecommerce platform. */
USE ecommerce;
select country,count(gender) number_of_users from users_data where gender='M' group by country ;

/*20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country. */
USE ecommerce;
SELECT AVG(productsSold) Average_product_sold,AVG(productsBought) Average_product_bought,country FROM users_data WHERE gender='M' GROUP BY country;