Project 1
/* Business model Customer to Customer (C2C) allows customers to do business with each other. This model is growing fast with e-commerce platforms where sellers may be required to pay some amount and buyer can buy it without paying anything. E-Commerce website brings the seller and buyer to the same platform.
Analyzing the user's database will lead to understanding the business perspective. Behaviour of the users can be traced in terms of business with exploration of the user’s database.
Dataset: One .csv file with name users_data with 98913 rows and 27 columns
Tasks to be performed*/
#1.Create new schema as ecommerce
create database ecommerce;
use ecommerce;
-- ----------------------------------------------------------------------------------------------------------------
#2.Import .csv file users_data into MySQL
-- imported the data
-- ----------------------------------------------------------------------------------------------------------------
#3.Run SQL command to see the structure of table
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),count(distinct language) from users_data;
-- ----------------------------------------------------------------------------------------------------------------
#6.Check whether male users are having maximum followers or female users.
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*/
select 'hasProfilePicture',count(hasProfilePicture) from users_data where hasProfilePicture='true' union
select 'hasAnyApp',count(hasAnyApp) from users_data where hasAnyApp='true' union
select 'hasAndroidApp',count(hasAndroidApp) from users_data where hasAndroidApp='true'union
select 'hasIosApp',count(hasIosApp) 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 count(productsBought) BUYERS, country 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.)*/
select count(productsSold) sellers, country 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
select country,max(productsPassRate) from users_data group by country order by max(productsPassRate) desc limit 10;
-- ----------------------------------------------------------------------------------------------------------------
#11.Calculate the number of users on an ecommerce platform for different language choices
select count(identifierHash) , language Users 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.)*/
select 'F' gender, 'wished_products' choice ,count(productsWished) no_choices from users_data where gender='f' and productsWished>0 union
select 'F','liked_products' ,count(socialProductsLiked) 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.)
select 'M' gender, 'products sold' choice , COUNT(productsSold) no_choices from users_data where gender='m' and productsSold>0 union
select 'M','products Bought' ,COUNT(socialProductsLiked) from users_data where gender='m' and productsBought>0 ;
-- ----------------------------------------------------------------------------------------------------------------
#14.Which country is having maximum number of buyers?
select COUNT(productsBought) buyers,country from users_data group by country order by COUNT(productsBought) desc limit 1;
select count(productsBought) from users_data group by country ;
select * from users_data;
-- ----------------------------------------------------------------------------------------------------------------
#15.List the name of 10 countries having zero number of sellers.
-- use sub-query, find number of non sellers first then remove those countries who have sellers
SELECT country ,productsSold FROM users_data where 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 asc limit 110;
-- ----------------------------------------------------------------------------------------------------------------
#17.Calculate the number of female users those who have not logged in since last 100 days.
select count(daysSinceLastLogin) from users_data where daysSinceLastLogin>=100 and gender='f';
-- ----------------------------------------------------------------------------------------------------------------
#18.Display the number of female users of each country at ecommerce platform.
select gender,count(hasAnyApp) users,country from users_data where gender='f' and hasAnyApp='TRUE' group by country order by count(hasAnyApp) desc;
-- ----------------------------------------------------------------------------------------------------------------
#19.Display the number of male users of each country at ecommerce platform.
select gender,count(hasAnyApp) users,country from users_data where gender='m' and hasAnyApp='TRUE' group by country order by count(hasAnyApp) desc;
-- ----------------------------------------------------------------------------------------------------------------
#20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
select country,avg(productsSold),avg(productsBought) from users_data where gender ='M' and hasAnyApp = 'TRUE' group by country order by country asc;