Project 1

Unlocking Insights: Understanding C2C E-Commerce Through User Data:
C2C e-commerce connects customers for transactions. Sellers may pay fees, buyers don't. By analyzing user data, we grasp business insights and track user behavior.

1. Creating a new schema from e-commerce:
create database ecommerce;

2. Import .csv file users_data into MySQL:

use ecommerce;

3. SQL command to see the structure of the table:
select * from users_data;

4. Run the SQL command to select the first 100 rows of the database:
select * from users_data limit 100;

5. Distinct values exist in the table for field country and language:
select count(distinct country) distinct_country, count(distinct language) distinct_language from users_data;

6. Male users have the maximum number of followers than female users:
select gender, sum(socialNbFollowers) Followers from users_data group by gender;

There are more female followers than male followers.

7. Calculate the total users:
a.Uses Profile Picture in their Profile.
select * from users_data;
select count(hasProfilePicture) count_ProfilePic from users_data where hasProfilePicture = 'True';

b. Uses Application for E-commerce platform:
select count(hasAnyApp) count_Ecommerce_Application from users_data where hasAnyApp = 'True';

c. Uses Android app:

select * from users_data;
select count(hasAndroidApp) count_Andriod from users_data where hasAndroidApp = 'True';

d.Uses ios app:
select count(hasIosApp) count_IOS from users_data where hasIosApp = 'True';

8. The total number of buyers for each country and sort the result in descending order of the total number of buyers:
select country, count(productsBought) Total_Products from users_data where productsBought>=1 group by country order by sum(productsBought) desc;

9. 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.)

select * from users_data;
select country,count(productsSold)No_of_Products_Sold from users_data where productsSold>=1 group by country order by sum(productsSold) asc;

10. Name of top 10 countries having maximum product pass rate:

select * from users_data;
select country,round(sum(productsPassRate)) Peak_Efficiency from users_data group by country order by sum(productsPassRate) desc limit 10;

11. The number of users on an e-commerce platform for different language choices.

select language, count(type) Users from users_data group by language;

12. Choice of female users about putting the product in a wishlist or to like socially on an e-commerce platform

SELECT 'Female Choices', 'Count'

UNION

SELECT 'Wishlist',count(productsWished)

FROM users_data

WHERE gender='F' and productsWished>0

UNION

SELECT 'Ecommerce Platform',count(socialProductsLiked)

FROM users_data

WHERE gender='F' and socialProductsLiked>0;

13. Choice of male users about being sellers or buyers. (Hint: use UNION to solve this question.)

SELECT 'Male Choices', 'Count'

UNION

SELECT 'Wishlist',count(productsBought)

FROM users_data

WHERE gender='M' and productsBought>0

UNION

SELECT 'Ecommerce Platform', count(productsSold)

FROM users_data

WHERE gender='M' and productsSold>0;

14. Which Country is the maximum number of buyers?
France is the Country which has the max no of buyers

select * from users_data;

select country, sum(productsBought) Max_Buyers from users_data group by country order by sum(productsBought) desc limit 1;

15. List the names of 10 countries having zero number of sellers.

select country, productsSold from users_data where productsSold = 0 group by country limit 10;

16. Displaying the record of the top 110 users using e-commerce platforms recently.

select identifierHash, gender, daysSinceLastLogin from users_data order by daysSinceLastLogin asc limit 110;

17. The number of female users who have not logged in in the last 100 days.

select * from users_data;

select gender, count(daysSinceLastLogin) Last_login from users_data where gender = 'F' and daysSinceLastLogin>100;

18. Number of female users of each country at e-commerce platform.

select country, gender,count(type) Users from users_data where gender = 'F' group by country order by country asc ;

19. Number of male users of each country at e-commerce platform.

select country, gender, count(type) Users from users_data where gender = 'M' group by country order by country asc ;

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

select * from users_data;

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