Project 1_Business_model_Customer_to_Customer (C2C)

-- Question 1
-- create schema ecommerce;
use ecommerce;
-- Question 2
select * from users_data
limit 2;
select count(identifierHash) from users_data;
select count( distinct identifierHash ) from users_data;

-- 3. Run SQL command to see the structure of table
describe users_data;
desc users_data;

-- 4 Run SQL command to select first 100 rows of the database
select * from users_data
limit 100;

-- 5 How many distinct values exist in table for field country and language
select count( distinct country ) as Total_country ,
count( distinct language) from users_data;
-- check question
-- 6 Check whether male users are having maximum followers or female users.
-- method - 1
select 'Male' Gender_type ,sum(socialNbFollowers) Total from users_data
where gender = "M"
union
select 'Female' , sum(socialNbFollowers) Total from users_data
where gender = "F";
-- method - 2
select sum(socialNbFollowers) ,gender from users_data group by gender;

-- 7 Calculate the total users those
-- a Uses Profile Picture in their Profile
select * from users_data
limit 50;
select count(hasProfilePicture) from users_data
where hasProfilePicture = 'True';

-- b Uses Application for Ecommerce platform check (column name)
select count(hasAnyApp) Has_app from users_data
where hasAnyApp = 'True';

-- c Uses Android app
select count(hasAndroidApp)Android_app from users_data
where hasAndroidApp = 'True';

-- d Uses ios app

select count(hasIosApp) 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.)*/

select country,
count(productsBought)
from users_data
where productsBought>0
group by country ;
/*
select country,
count(identifierHash) over (partition by productsBought) Total_buyer
from users_data
where productsBought>=1
group by country
order by Total_buyer 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.)*/

select country ,
count(productsSold ) as Total_seller
from users_data
where productsSold>0
group by country
order by Total_seller ;

-- 10 Display name of top 10 countries having maximum products pass rate.
select country ,
max(productsPassRate)
from users_data
group by country
order by max(productsPassRate) desc
limit 10;
/*
SELECT DISTINCT
country, min(productsPassRate)
FROM users_data
group by country
ORDER BY productsPassRate DESC LIMIT 10; */

-- 11 Calculate the number of users on an ecommerce platform for
-- different language choices.

select distinct (language) ,
count(language) as user
from users_data
where hasAnyApp = 'True'
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.) socialProductsLiked//hasAnyApp
*/
select 'product_liked' list, count(socialProductsLiked) choice from users_data
where gender ='F'and socialProductsLiked>0
union
select "product_wishlist", count(productsWished) from users_data
where gender = 'F'and productsWished>0;



-- 13 Check the choice of male users about being seller or buyer.
-- (Hint: use UNION to solve this question.) productsSold/productsBought
select 'Seller_choice' Type, count( productsSold) total_choice from users_data
where gender ="M" and productsSold>0
union
select ' Buyer_choice' , count(productsBought) from users_data
where gender = "M" and productsBought>0;

-- 14 Which country is having maximum number of buyers?
select country ,
max(count(productsBought) )over (order by identifierHash ) total from users_data
group by country
order by total desc
limit 1;

-- 15 List the name of 10 countries having zero number of sellers.
-- method 1
select country , count(productsSold)
from users_data
group by country
order by count(productsSold)
limit 10;
-- method 2
select country from users_data
where productsSold = 0
group by country
having country not in (select country from users_data
where productsSold != 0
group by country )
limit 10;

-- 16 Display record of top 110 users who have used ecommerce platform recently.
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.
select count(gender) from users_data
where daysSinceLastLogin>=100 and gender ='F';

-- 18 Display the number of female users of each country at ecommerce platform.
select country , count(gender) from users_data
where hasAnyApp = "True" and gender = "F"
group by country;

-- 19 Display the number of male users of each country at ecommerce platform.

select country , count(gender) from users_data
where hasAnyApp = "True" and gender = "M"
group by country;

-- 20 Question 20

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