SQL Project on e-commerce database

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

use ecommerce;

select * from data;

-- by import option,all the data from users_data file is imported in ecommerce database

----------------------------------------------------------------------------------------------------------------------------

-- 3.Run SQl command to see the structure of table

use ecommerce;
desc data;

----------------------------------------------------------------------------------------------------------------------------

-- 4.Run sql command to select first 100 rows of table

use ecommerce;
select * from data limit 100;

----------------------------------------------------------------------------------------------------------------------------

-- 5. How many distinct values exist in table for fields country and language

use ecommerce;
select count(distinct country) distinct_country , count(distinct language) distinct_language from data;

----------------------------------------------------------------------------------------------------------------------------

-- 6. Check whether male users are having more followers or female users

use ecommerce;
select gender,sum(socialNbFollowers) followers from data group by gender;
-- Of course female users have more followers

----------------------------------------------------------------------------------------------------------------------------

-- 7. a) total users who uses profile picture in their profile

use ecommerce;
select count(*) total_users_with_prfile_picture from data where hasProfilePicture='True';

----------------------------------------------------------------------------------------------------------------------------

-- 7. b) Total users who uses application for ecommerce platform

use ecommerce;
select count(*) total_users from data where hasAnyApp='True';

----------------------------------------------------------------------------------------------------------------------------

-- 7. c) Total users who uses android application

use ecommerce;
select count(*) total_users from data where hasAndroidApp='True';

----------------------------------------------------------------------------------------------------------------------------

-- 7. d) Total users who uses IOS app

use ecommerce;
select count(*) total_users from data where hasIosApp='True';

----------------------------------------------------------------------------------------------------------------------------

-- 8. To calculate the total number of buyers for each country and sort the result in descending order of total number of buyers

use ecommerce;
select country ,sum(productsBought) Total_buyers from data group by country order by sum(productsBought) desc;

----------------------------------------------------------------------------------------------------------------------------

-- 9. To calculate the average number of sellers for each country and sort the result in ascending order of total number of sellers

use ecommerce;
select country ,avg(productsSold) Total_Sellers from data group by country order by avg(productsSold) asc;

----------------------------------------------------------------------------------------------------------------------------

-- 10. Displaying name of top 10 countries having maximum products pass rate

use ecommerce;
select country,sum(productsPassRate) Products_Pass_Rate from data group by country order by sum(productsPassRate) desc limit 10;

----------------------------------------------------------------------------------------------------------------------------

-- 11. To calculate the number of users on an ecommerce platform for different language choices.

use ecommerce;
select language,count(*) Users from 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.

use ecommerce;
select gender,sum(productsWished) ProductsWished , sum(socialProductsLiked) ProductsLiked from data where gender='F';
-- Female users chose more to like socially on ecommerce platform than putting product in wishlist.

----------------------------------------------------------------------------------------------------------------------------

-- 13. choice of male users about being seller or buyer

use ecommerce;
select gender,sum(productsSold) Products_Sold , sum(productsBought) Products_Bought from data where gender='M';
-- From above data it is clear that the Male users are chose to be the buyers more

----------------------------------------------------------------------------------------------------------------------------

-- 14. country with maximum number of buyers

use ecommerce;
select country , sum(productsBought) ProductsBought from data group by country order by sum(productsBought) desc limit 1;

----------------------------------------------------------------------------------------------------------------------------

-- 15. List the name of 10 countries having zero number of sellers

use ecommerce;
select country, (productsSold) from data where productsSold=0 group by country limit 10;

----------------------------------------------------------------------------------------------------------------------------

-- 16. Displaying record of top 110 users who have used ecommerce platform recently.

use ecommerce;
select identifierHash,gender, daysSinceLastLogin from data order by daysSinceLastLogin asc limit 110;

----------------------------------------------------------------------------------------------------------------------------

-- 17. the number of female users those who have not logged in since last 100 days.

use ecommerce;
select gender,count(daysSinceLastLogin) No_of_femaleUsers from data where gender='F' and daysSinceLastLogin > 100;

----------------------------------------------------------------------------------------------------------------------------

-- 18. Display the number of female users of each country at ecommerce platform.

use ecommerce;
select gender,country,count(*) No_of_FemaleUsers from data where gender='F' group by country order by country asc;

----------------------------------------------------------------------------------------------------------------------------

-- 19. Display the number of male users of each country at ecommerce platform

use ecommerce;
select gender,country,count(*) No_of_maleUsers from data where gender='M' group by country order by country asc;

----------------------------------------------------------------------------------------------------------------------------

-- 20. Calculate the average number of products sold and bought on ecommerce platform by male users for each country.

use ecommerce;
select country,gender,avg(productsSold) , avg(productsBought) from data where gender='M' group by country order by country asc;

----------------------------------------------------------------------------------------------------------------------------

kindly, Review the project.

Regards,

Sai Sumanth Reddy