Project 1
All the given task has been performed for Business model Customer to Customer (C2C) and same .sql file attached below.
create database ecommerce;
use ecommerce;
select * from users_data;
DESC users_data;
# first 100 rows of the database
select * from users_data limit 100;
# distinct values exist in table for field country and language
select count(distinct country) as dist_country, count(distinct language) as distinct_language from users_data;
# whether male users are having maximum followers or female users.
select sum(socialNbFollowers) as max_followers ,gender from users_data group by gender;
#Calculate the total users those
#Uses Profile Picture in their Profile
SELECT COUNT(*) as profile_picture FROM users_data WHERE hasProfilePicture ="True";
#Uses Application for Ecommerce platform
select count(*) Any_app from users_data where hasAnyApp = "True";
# Uses Android app
select count(*) as Android_app from users_data where hasAndroidApp = "True";
# Uses ios app
select count(*) as ios_app from users_data where hasIosApp = "True";
# total number of buyers for each country and sort the result in descending order of total number of buyers.
select country,sum(productsBought) as buyers from users_data group by country order by sum(productsBought) desc;
#total number of sellers for each country and sort the result in ascending order of total number of sellers.
select country,sum(productsSold) as sellers from users_data group by country order by sum(productsSold) asc;
# Display name of top 10 countries having maximum products pass rate.
select country, sum(productsPassRate) as maximum_products_pass_rate from users_data group by country order by sum(productsPassRate) desc limit 10;
# Calculate the number of users on an ecommerce platform for different language choices.
select language, count(*) as Different_Language from users_data group by language;
#Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform.
SELECT SUM(productsWished) as wishlist,sum(socialProductsLiked) as like_socially FROM users_data where gender='F';
# Check the choice of male users about being seller or buyer.
select sum(productsSold) as seller,sum(productsBought) as buyer from users_data where gender = 'M';
# Which country is having maximum number of buyers?
select country,sum(productsBought) as maximum_buyers from users_data group by country order by sum(productsBought) desc limit 1;
# List the name of 10 countries having zero number of sellers.
select country,productsSold from users_data where productsSold = 0 limit 10;
# Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM users_data ORDER BY daysSinceLastLogin asc LIMIT 110;
# Calculate the number of female users those who have not logged in since last 100 days.
select gender,count(daysSinceLastLogin) since_last_100_days from users_data where gender = 'F' and daysSinceLastLogin > 100;
# Display the number of female users of each country at ecommerce platform.
select country,count(*) as female_Users from users_data where gender = 'F' group by country;
# Display the number of male users of each country at ecommerce platform.
select country,count(*) as male_Users from users_data where gender = 'M' group by country;
# Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
select country,avg(productsSold) avg_sold_by_male,avg(productsBought) avg_bought_by_male from users_data where gender = 'M' group by country;