Project 1

/*
Create new schema as ecommerce
*/
create database 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)
*/

/*
Run SQL command to see the structure of table
*/
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 ) country , count(distinct language ) language from users_data;

/*
Check whether male users are having maximum followers or female users.
*/
select gender,sum(socialNbFollowers) 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 * from users_data;
select type,hasProfilePicture,hasAnyApp,hasAndroidApp,hasIosApp from users_data;

SELECT COUNT(*) AS total_users_with_profile_picture
FROM users_data
WHERE hasProfilePicture = 'True';

SELECT COUNT(*) AS Application_for_Ecommerce_platform
FROM users_data
WHERE hasAnyApp = 'True';

SELECT COUNT(*) AS hasAndroidApp
FROM users_data
WHERE hasAndroidApp = 'True';

SELECT COUNT(*) AS 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 * from users_data;
SELECT
country, COUNT(DISTINCT identifierHash) AS USERS
FROM
users_data
WHERE
productsBought >= 1
GROUP BY COUNTRY
ORDER BY USERS 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 * from users_data;
SELECT country, COUNT(DISTINCT identifierHash) AS total_sellers
FROM users_data
WHERE productsSold >= 1
GROUP BY country
ORDER BY total_sellers ASC;

/*
Display name of top 10 countries having maximum products pass rate.
*/
select * from users_data;
SELECT country, AVG(productsPassRate) AS avg_pass_rate
FROM users_data
GROUP BY country
ORDER BY avg_pass_rate DESC
LIMIT 10;

/*
Calculate the number of users on an ecommerce platform for different language choices.
*/
select * from users_data;
SELECT LANGUAGE,COUNT(DISTINCT identifierHash) 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 * from users_data;
SELECT 'wishlist' AS choice, COUNT(*) AS total_female_users
FROM users_data
WHERE gender = 'f' AND productsWished > 0

UNION

SELECT 'social like' AS choice, COUNT(*) AS total_female_users
FROM users_data
WHERE gender = 'f' AND socialProductsLiked > 0;

/*
Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
*/
select * from users_data;
select 'seller' as choice,count(*) as total_male_users from users_data where gender='M' AND productsSold>0
union
select 'buyer' as choice,count(*) as total_male_users from users_data where gender='M' AND productsBought>0;

/*
Which country is having maximum number of buyers?
*/
select * from users_data;
SELECT country, COUNT(DISTINCT identifierHash) AS total_buyers
FROM users_data
WHERE productsBought > 0
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;

/*
List the name of 10 countries having zero number of sellers.
*/

SELECT country, COUNT(*) AS num_sellers
FROM users_data
WHERE productsBought >= 1 AND productsSold = 0
GROUP BY country
LIMIT 10;

/*
Display record of top 110 users who have used ecommerce platform recently.
*/
SELECT * FROM USERS_DATA;
SELECT *
FROM users_data
WHERE hasAnyApp = 'TRUE'
ORDER BY daysSinceLastLogin DESC
LIMIT 110;

/*
Calculate the number of female users those who have not logged in since last 100 days.
*/
SELECT * FROM USERS_DATA;
SELECT GENDER,COUNT(DISTINCT identifierHash) FROM USERS_DATA
WHERE GENDER='F' AND daysSinceLastLogin>=100 GROUP BY GENDER;

/*
Display the number of female users of each country at ecommerce platform.
*/
SELECT * FROM USERS_DATA;
SELECT country, COUNT(*) AS num_female_users
FROM users_data
WHERE gender = 'F'
GROUP BY country;

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