Business model Customer to Customer (C2C)
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.
Creating Database - Ecommerce
The following tasks are performed on database to analyse the database
----------------------------------------------------------------------------------------------------------------------------
/*
1.Create new schema as ecommerce*/
create database 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)*/
/*
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 SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;
/*
7.Calculate the total users those
a.Uses Profile Picture in their Profile
*/
USE ecommerce;
select count(hasProfilePicture) Total_Profile_Picture from users_Data where hasProfilePicture='True';
/*
b.Uses Application for Ecommerce platform
*/
USE ecommerce;
select count(hasAnyApp) Total_Application FROM users_data WHERE hasAnyApp ="True";
/*c.Uses Android app*/
USE ecommerce;
SELECT COUNT(hasAndroidApp) total_use_of_android_app FROM users_data WHERE hasAndroidApp ="True";
/*d.Uses ios app*/
USE ecommerce;
SELECT COUNT(hasIosApp) total_Use_of_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.)
*/
USE ecommerce;
SELECT country,SUM(productsBought) FROM users_data GROUP BY country ORDER BY SUM(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.)
*/
USE ecommerce;
SELECT country,AVG(productsSold) FROM users_data GROUP BY country ORDER BY AVG(productsSold) ASC;
/*
10.Display name of top 10 countries having maximum products pass rate.
*/
USE ecommerce;
SELECT country,SUM(productsPassRate) 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.
*/
USE ecommerce;
SELECT language, COUNT(*) number_of_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. (Hint: use UNION to answer this question.)
*/
USE ecommerce;
SELECT SUM(productsWished) Sum_of_Products_wished,SUM(socialProductsLiked) Sum_of_products_liked FROM users_data WHERE gender='F';
/*
13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
*/
USE ecommerce;
SELECT SUM(productsSold),SUM(productsBought) FROM users_data WHERE gender='M';
/*
14.Which country is having maximum number of buyers?
*/
USE ecommerce;
select country ,SUM(productsBought) Sum_of_product_bought FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC;
/*15. List the name of 10 countries having zero number of sellers. */
USE ecommerce;
SELECT productsSold,country FROM users_data WHERE productsSold=0 LIMIT 10;
/*16.Display record of top 110 users who have used ecommerce platform recently.*/
USE ecommerce;
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.*/
USE ecommerce;
SELECT gender,COUNT(daysSinceLastLogin) number_of_femal_user_not_logged FROM users_data WHERE gender='F' AND daysSinceLastLogin >100;
/*
18.Display the number of female users of each country at ecommerce platform.
*/
USE ecommerce;
select country,count(*) number_of_users from users_data where gender='F' group by country ;
/*19.Display the number of male users of each country at ecommerce platform. */
USE ecommerce;
select country,count(*) number_of_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. */
USE ecommerce;
SELECT AVG(productsSold) Average_product_sold,AVG(productsBought) Average_product_bought,country FROM users_data WHERE gender='M' GROUP BY country;