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
# 1 st question Create new schema as ecommerce
# ans is created
#2nd 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)
# Ans donit
# 3rd Run SQL command to see the structure of table
DESCRIBE ecommerce.users_data;
# 4th Run SQL command to select first 100 rows of the database
SELECT * FROM ecommerce.users_data LIMIT 100;
# 5th How many distinct values exist in table for field country and language
SELECT COUNT(DISTINCT country) AS distinct_countries, COUNT(DISTINCT language) AS distinct_languages FROM ecommerce.users_data;
# 6th Check whether male users are having maximum followers or female users.
(SELECT MAX(socialNbFollowers) FROM ecommerce.users_data);
#7th Calculate the total users those
# A question
# A Uses Profile Picture in their Profile
(select sum(hasprofilepicture) from ecommerce .users_data);
# B question
# Uses Application for Ecommerce platform
# Ans is
SELECT COUNT(hasanyapp) from users_data where hasanyapp="TRUE";
# C question
# Uses Android app
SELECT COUNT(*) from users_data where hasAndroidApp="TRUE";
# D question
# Uses ios app
SELECT COUNT(*) from users_data where hasiosapp="TRUE";
# 8 question
# 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.)
# Ans
select count(productsbought),country from users_data where productsBought >0 group by country;
#9th question
# 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.)
# Ans
select count(productsbought),country from users_data where productsSold >0 group by country;
# 10 quetion
# Display name of top 10 countries having maximum products pass rate.
# Ans
SELECT country ,max(productsPassRate) FROM users_data GROUP BY country ORDER BY max(productsPassRate) DESC LIMIT 10;
#11 th quesstion
# Calculate the number of users on an ecommerce platform for different language choices.
# Ans
SELECT language, COUNT(*) AS user_count FROM users_data GROUP BY language;
# select * from users_data;
#12 th question
# 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.)
# Ans
SELECT productswished, COUNT(*) AS female_users_count FROM users_data WHERE gender = 'female' GROUP BY productswished;
# 13 question
# Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
#Ans
SELECT "male choice","count"union
select "seller", count(productsSold) from users_data where gender='M' and productsSold>0
union
select "buyer", count(productsBought) from users_data where gender='M' and productsBought>0;
# 14 Question
#Which country is having maximum number of buyers?
SELECT count(productsBought),country
FROM users_data where productbought>=1
GROUP BY country
ORDER BY count(productsBought) DESC
LIMIT 1;
# 15 th qeustion
# List the name of 10 countries having zero number of sellers.
# Ans
SELECT country ,count(productsSold)from users_data where productsSold=0 group by country limit 10;
# 16 th question
# Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM users_data ORDER BY daysSinceLastLogin DESC LIMIT 110;
# 17 th qestion
# Calculate the number of female users those who have not logged in since last 100 days.
select gender ,daysSinceLastLogin from users_data where gender="F" AND daysSinceLastLogin > 100;
# 18th question
# Display the number of female users of each country at ecommerce platform.
SELECT country, COUNT(gender) FROM users_data WHERE gender ="F" and hasAnyApp = "True" group BY country;
#19 th question
# Display the number of male users of each country at ecommerce platform.
# 20 th question
# Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT country, AVG(productsSold) as avg_productsSold, AVG(productsBought) as avg_productsBought FROM users_data WHERE gender = "M" GROUP BY country;