Project 1

# SQL Project 1

/*
1.Create new schema as ecommerce
*/
-- database created as ecommerce

/*
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)
*/
-- file imported

/*
3.Run SQL command to see the structure of table
*/
USE ecommerce;
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 gender,sum(socialNbFollowers) from users_data group by gender;

/*
7.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
*/
#a
USE ecommerce;
select count(hasProfilePicture) from users_data where hasProfilePicture LIKE 'T%';

#b
USE ecommerce;
select count(hasAnyApp) from users_data where hasAnyApp = 'TRUE' ;
select count(hasAndroidApp) from users_data where hasAndroidApp = 'TRUE';
select count(hasIosApp) from users_data where hasIosApp = 'TRUE';
select 26174 + 4819 + 21527; -- = 52520

#c
USE ecommerce;
select count(hasAndroidApp) from users_data where hasAndroidApp LIKE 'T%';

#d
USE ecommerce;
select count(hasIosApp) from users_data where hasIosApp LIKE 'T%';

/*

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 country,count(productsBought) buyers from users_data where productsBought >1 group by country order by buyers 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) sellers from users_data where productsBought >=1 group by country order by sellers asc;

/*
10.Display name of top 10 countries having maximum products pass rate.
*/
USE ecommerce;
SELECT country, COUNT(productsPassRate) MaxProductsPassRate
FROM users_data
WHERE productsPassRate > 0
GROUP BY country
order by MaxProductsPassRate desc limit 10;

/*
11.Calculate the number of users on an ecommerce platform for different language choices.
*/
select language,count(type) from users_data where hasAnyApp = 'TRUE' group by language; -- no of user on hasAnyApp
select language,count(type) user from users_data where hasAndroidApp = 'TRUE' group by language; -- no of user on hasAndroidApp
select language,count(type) from users_data where hasIosApp = 'TRUE' group by language; -- no of user on hasIosApp

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

*/

SELECT 'productsWished' as 'female', count(TYPE) FROM users_data WHERE gender = 'F' and productsWished > 0 union
SELECT 'socialProductsLiked' as 'female', count(TYPE) FROM users_data WHERE gender = 'F' and socialProductsLiked > 0 ;

/*
13.Check the choice of male users about being seller or buyer.
(Hint: use UNION to solve this question.)
*/
USE ecommerce;
SELECT 'productsBought' as 'male', count(TYPE) FROM users_data WHERE gender = 'M' and productsBought > 0 union
SELECT 'productsSold' as 'male', count(TYPE) FROM users_data WHERE gender = 'M' and productsSold > 0 ;

/*
14.Which country is having maximum number of buyers?
*/
use ecommerce;
select country,count(productsBought) buyers from users_data where productsBought > 0 group by country order by buyers desc limit 1;

/*
15.List the name of 10 countries having zero number of sellers.
*/
select country,count(productsSold) buyers from users_data group by country order by buyers asc limit 10;

/*
16.Display record of top 110 users who have used ecommerce platform recently.
*/
select identifierHash ,daysSinceLastLogin from users_data order by daysSinceLastLogin asc limit 110;

/*
17.Calculate the number of female users those who have
not logged in since last 100 days.
*/
select count(identifierHash) from users_data where gender = 'F' and daysSinceLastLogin >= 100;

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

/*
19.Display the number of male users of each country at ecommerce platform.
*/
select country, count(type) female_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.

*/
select country,count(type) Male_users ,avg(productsSold),avg(productsBought) from users_data where gender = 'M' group by country;