Project 1
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.
SQL file Attached as per the task :
#==================================================================
#1Create new schema as ecommerce
create database ecommerces;
use ecommerces;
#==================================================================
# 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)
#==================================================================
#3 Run SQL command to see the structure of table
select * from 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 language,country from users_data;
select count(distinct country),count(distinct language) from users_data;
#==================================================================
#6Check whether male users are having maximum followers or female users.
select socialNbFollowers,socialNbFollows from users_data;
select gender,max(socialNbFollowers) from users_data group by gender;
#==================================================================
#7
#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 'Profile Picture',count(hasProfilePicture) from users_data where hasProfilePicture='True'
union
select 'ios app',count(hasIosApp) from users_data where hasIosApp='True'
union
select 'Android app',count(hasAndroidApp) from users_data where hasAndroidApp='True'
union
select 'Application for Ecommerce platform',count(hasAnyApp) from users_data where hasAnyApp='True';
#==================================================================
#8
#Calculate the total number of buyers for each country and
#sort the result in descending order of total number of buyers.
select country,sum(productsBought) total_number_of_buyer from users_data group by country
order by total_number_of_buyer desc;
#==================================================================
#9
#Calculate the average number of sellers for each country and sort the result in ascending
#order of total number of sellers.
select country,avg(productsSold) total_number_of_sellers from users_data group by country
order by sum(productsSold) asc;
#select country,avg(productsSold) total_number_of_sellers from users_data group by country
#order by total_number_of_sellers asc;
#10=================================================================#Display name of top 10 countries having maximum products pass rate.
select country,sum(productsPassRate) top from users_data group by country order by sum(productsPassRate) desc limit 10;
#==================================================================
#11
#Calculate the number of users on an ecommerce platform for different language choices.
select * from users_data;
select language ,count(type) 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.
select * from users_data;
select gender,sum(productsWished), sum(socialProductsLiked) from users_data where gender='F' and hasAnyApp='True';
#==================================================================
#13
#Check the choice of male users about being seller or buyer.
select gender,sum(productsBought),sum(productsSold) from users_data where gender='M';
#==================================================================
#14
#Which country is having maximum number of buyers?
select country,sum(productsBought) buyer 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.
select * from users_data;
select country,sum(productsSold) sellers from users_data group by country order by sum(productsSold) 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 gender='F' and daysSinceLastLogin>100;
#==================================================================
#18
#Display the number of female users of each country at ecommerce platform.
select country,count(gender) female 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(gender) mail 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 * from users_data;
select avg(productsSold),avg(productsBought),gender,country from users_data where gender='M' group by country ;
#==================================================================