Project 1

Aim : 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. Create new schema as ecommerce
2. Import .csv file users_data into MySQL
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
1. Uses Profile Picture in their Profile
2. Uses Application for Ecommerce platform
3. Uses Android app
4. 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.
9. Display name of top 10 countries having maximum products pass rate.
10. Calculate the number of users on an ecommerce platform for different language choices.
11. Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform.
12. Which country is having maximum number of buyers?
13. List the name of 10 countries having zero number of sellers.
14. Display record of top 110 users who have used ecommerce platform recently.
15. Calculate the number of female users those who have not logged in since last 100 days.
16. Display the number of female users of each country at ecommerce platform.
17. Display the number of male users of each country at ecommerce platform.
18. Calculate the average number of products sold and bought on ecommerce platform by male users for each country.

Query are written as follows:

-- Q1 Ans

CREATE SCHEMA ecommerce;

USE ecommerce;

-- Q2 Ans

-- Q3 Ans

DESC users_data;

-- Q4 Ans

SELECT * FROM users_data LIMIT 100;

-- Q5 Ans

SELECT COUNT(distinct country) country, COUNT(distinct language) language FROM users_data;

-- Q6 Ans

SELECT gender,sum(socialNBFollowers) FROM users_data GROUP BY gender;

-- Female users have maximum followers than male users.

-- Q7 Ans

-- a ans

SELECT count(hasProfilePicture) Users_with_ProfilePicture FROM users_data WHERE hasProfilePicture = 'True';

-- b ans

SELECT count(hasAnyApp) Users_with_Anyapp FROM users_data WHERE hasAnyApp = 'True';

-- c ans

SELECT count(hasAndroidApp) Users_with_AndroidApp FROM users_data WHERE hasAndroidApp = 'True';

-- d ans

SELECT count(hasIosApp) Users_with_IosApp FROM users_data WHERE hasIosApp = 'True';

-- Q8 Ans

SELECT country, COUNT(ProductsBought) Buyers FROM users_data GROUP BY country ORDER BY Buyers DESC ;

-- Q9 Ans

SELECT country, COUNT(ProductsSold) Sellers FROM users_data GROUP BY country ORDER BY Sellers ASC;

-- Q10 Ans

SELECT country FROM users_data GROUP BY country having max(productsPassRate) LIMIT 10;

-- Q11 Ans

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

-- Q12 Ans

select sum(productsWished) ,sum(socialProductsLiked) from users_data where gender='F';

-- choice of ehr female users about putting the product in a wishlist or to like socially om an ecommerce platform is same.

-- Q13 Ans

select count(productsSold),count(productsBought) from users_data where gender='M';

-- choicr of male users about being seller or buyer is same.

-- Q14 Ans

select country,max(productsBought) Max_no_of_buyers from users_data group by country order by max(productsBought) desc limit 1;

-- Belfique country is having maximum number of buyers.

-- Q15 Ans

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

-- Q16 Ans

select * from users_data group by daysSinceLastLogin having min(daysSinceLastLogin) order by daysSinceLastLogin asc limit 110;

-- Q17 Ans

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

-- Q18 Ans

select country,count(gender) Female_users from users_data where gender='F' group by country;

-- Q19 Ans

select country,count(gender) Male_users from users_data where gender='M' group by country;

-- Q20 Ans

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