Project 1 - Business model Customer to Customer (C2C) using MySQL
-- <<< PROJECT 1 >>>
-- Info of the Project to be extracted or simplified: for the data in users_data.csv
/*
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
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
4. Run SQL command to select first 100 rows of the database
5. How many distinct values exist in table for field country and language
6. Check whether male users are having maximum followers or female users.
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
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.)
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.)
10. Display name of top 10 countries having maximum products pass rate.
11. Calculate the number of users on an ecommerce platform for different language choices.
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.)
13. Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
14. Which country is having maximum number of buyers?
15. List the name of 10 countries having zero number of sellers.
16. Display record of top 110 users who have used ecommerce platform recently.
17. Calculate the number of female users those who have not logged in since last 100 days.
18. Display the number of female users of each country at ecommerce platform.
19. Display the number of male users of each country at ecommerce platform.
20. Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
*/
USE ECOMMERCE;
SELECT * FROM users_data; -- 98913 rows and 24 columns
-- Task 1. ----- CREATE SCHEMA -----DONE
-- Task 2. -----IMPORT DB TABLE from users_data.csv 'file'-----DONE
-- Task 3: Structure of Table
DESC users_data;
-- Task 4:
SELECT * FROM users_data LIMIT 100;
-- Task 5:
SELECT count(distinct country) countries, count(distinct language) languages FROM users_data; -- 200 countries and 5 languages
-- Task 6:
SELECT gender, sum(socialNBFollowers) FROM users_data GROUP BY gender; -- Female followers 262458 and Male followers 77038
-- Task 7:
# 7.1
SELECT count(hasProfilePicture) 'Users With Profile pictures' FROM users_data WHERE hasProfilePicture = 'True'; -- Users With Profile pictures : 97018
# 7.2
SELECT count(hasAnyApp) 'Any APP USERS' FROM users_data WHERE hasAnyApp = 'True'; -- Any APP USERS : 26174
# 7.3
SELECT count(hasAndroidApp) 'Android Users' FROM users_data WHERE hasAndroidApp = 'True'; -- Android app users : 4819
# 7.4
SELECT count(hasIosApp) 'IOS Users' FROM users_data WHERE hasIosApp = 'True'; -- Ios app useres : 21527
-- Task 8:
SELECT country, count(productsBought) Buyers FROM users_data GROUP BY country ORDER BY Buyers DESC;
-- Task 9:
SELECT country, count(productsSold) Sellers FROM users_data GROUP BY country ORDER BY Sellers ASC;
-- Task 10:
SELECT country 'Top Countries' FROM users_data GROUP BY country HAVING MAX(productsPassRate) LIMIT 10;
-- Task 11:
SELECT language, count(language) USERS FROM users_data GROUP BY language;
-- Task 12:
SELECT sum(productsWished) 'Wished Products count by females', sum(socialProductsLiked) 'Liked Products count by females'
FROM users_data WHERE gender='F'; -- 129498, 373600
-- Task 13:
SELECT count(productsSold) 'Males count who wanna be Buyers', count(productsBought) 'Males count who wanna be Buyers'
FROM users_data WHERE gender = 'M'; -- 22792, 22792
-- Task 14:
SELECT country, MAX(productsBought) 'No Of Buyers' FROM users_data GROUP BY country ORDER BY MAX(productsBought) DESC LIMIT 1; -- Belgique : 405
-- Task 15:
SELECT country '10 Countries Without Sellers' , productsSold FROM users_data WHERE productsSold = 0 LIMIT 10;
-- Task 16:
SELECT * FROM users_data GROUP BY daysSinceLastLogin HAVING min(daysSinceLastLogin) ORDER BY daysSinceLastLogin ASC LIMIT 110;
-- Task 17:
SELECT count(type) FROM users_data WHERE gender = 'F' and daysSinceLastLogin >= 100; -- 70212
-- Task 18:
SELECT country, count(gender) 'Female Users' FROM users_data WHERE gender = 'F' GROUP BY country;
-- Task 19:
SELECT country, count(gender) 'Male Users' FROM users_data WHERE gender = 'M' GROUP BY country;
-- Task 20:
SELECT country, avg(productsSold) 'Average Products Sold by MALE users', avg(productsBought) 'Average Products Bought by MALE users'
FROM users_data WHERE gender = 'M' GROUP BY country;
-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- ---------- This Whole Project is about to extract different data from the users_data of E-Commerce company ------ by MOHAMMED MUBEENUDDIN --------------------------------
-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Link of Files : shorturl.at/juDJR