Project 1

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.

# Answer 1
CREATE DATABASE ecommerce;

# Answer 2
-- Importing user_data.csv into the ecommerce database

# Answer 3
USE ecommerce;
DESC users_data;

# Answer 4
SELECT * FROM users_data LIMIT 100;

# Answer 5
SELECT
COUNT(DISTINCT country), COUNT(DISTINCT language)
FROM
users_data;

# Answer 6 (female users have more followers)
SELECT
SUM(socialNbFollowers), gender
FROM
users_data
GROUP BY gender;

# Answer 7
SELECT 'has Profile picture' Users, COUNT(hasProfilePicture) No_of_users
FROM users_data WHERE hasProfilePicture = 'True'
UNION SELECT 'has Ecommerce platform', COUNT(hasAnyApp)
FROM users_data WHERE hasAnyApp = 'True'
UNION SELECT 'has Android App', COUNT(hasAndroidApp)
FROM users_data WHERE hasAndroidApp = 'True'
UNION SELECT 'has Ios App', COUNT(hasIosApp)
FROM users_data WHERE hasIosApp = 'True';

# Answer 8
SELECT
country, COUNT(productsBought) Total_no_of_Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY country
ORDER BY Total_no_of_Buyers DESC;

# Answer 9
SELECT
country, COUNT(productsSold) Total_no_of_Sellers
FROM
users_data
WHERE
productsSold > 0
GROUP BY country
ORDER BY Total_no_of_Sellers;

# Answer 10
SELECT
country, SUM(productsPassRate) Max_Products_PassRate
FROM
users_data
WHERE
productsPassRate > 0
GROUP BY country
ORDER BY Max_Products_PassRate DESC
LIMIT 10;

# Answer 11
SELECT
language, COUNT(hasAnyApp) No_of_users_Ecommerce_platform
FROM
users_data
WHERE
hasAnyApp = 'True'
GROUP BY language;

# Answer 12 (Choice of female is like socially)
SELECT
'Wish list' Product, SUM(productsWished) Total_users
FROM
users_data
WHERE
gender = 'F' AND productsWished > 0
UNION SELECT
'Liked', SUM(socialProductsLiked)
FROM
users_data
WHERE
gender = 'F' AND socialProductsLiked > 0;

# Answer 13 (Male users prefer to be buyers)
SELECT
'Sellers' Choice, COUNT(productsSold) Male_users
FROM
users_data
WHERE
gender = 'M' AND productsSold > 0
UNION SELECT
'Buyers', COUNT(productsBought)
FROM
users_data
WHERE
gender = 'M' AND productsBought > 0;

# Answer 14 (France)
SELECT
country, COUNT(productsBought) Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY country
ORDER BY Buyers DESC
LIMIT 1;

# Answer 15
SELECT
country, productsSold Zero_sellers
FROM
users_data
WHERE
productsSold = 0
LIMIT 10;

# Answer 16
SELECT
*
FROM
users_data
WHERE
hasAnyApp = 'True'
ORDER BY daysSinceLastLogin
LIMIT 110;

# Answer 17
SELECT
COUNT(gender) Female_users
FROM
users_data
WHERE
gender = 'F'
AND daysSinceLastLogin > 100;

# Answer 18
SELECT
country, COUNT(gender) Female_users
FROM
users_data
WHERE
gender = 'F' AND hasAnyApp = 'True'
GROUP BY country;

# Answer 19
SELECT
country, COUNT(gender) Male_users
FROM
users_data
WHERE
gender = 'M' AND hasAnyApp = 'True'
GROUP BY country;

# Answer 20
SELECT
country, AVG(productsSold), AVG(productsBought)
FROM
users_data
WHERE
gender = 'M' AND hasAnyApp = 'True'
GROUP BY country;