Project 1

Analyzing the user's database of a customer to customer(C2C) business model to understand the business perspective. To trace the Behaviour of the users in terms of business with exploration of the user’s database.

Findings

5. Country 200 language 5

6. Male 262458 Female 77038

7.a.97018 b.26174 c.4819 d.21527

11.

en	51564
de	7178
fr	26372
es	6033
it	7766

12. Choice of female

products wished	129498
products liked	373600

13. Choice of Male

Products Sold	22792
Products bought	22792

14.Max no. of buyers

France

25135

Codes

-- Create new schema as ecommerce
Use ecommerce;

-- Run SQL command to see the structure of table
Desc users_data;

-- Run SQL command to select first 100 rows of the database
SELECT
*
FROM
users_data
LIMIT 100;
-- How many distinct values exist in table for field country and language
SELECT
COUNT(DISTINCT country), COUNT(DISTINCT language)
FROM
users_data;
-- Check whether male users are having maximum followers or female users.
SELECT
gender, SUM(socialNbFollowers)
FROM
users_data
GROUP BY gender;
-- Calculate the total users those
-- Uses Profile Picture in their Profile
SELECT
COUNT(hasProfilePicture), hasProfilePicture
FROM
users_data
GROUP BY hasProfilePicture;
-- Uses Application for Ecommerce platform
SELECT
COUNT(hasAnyApp), hasAnyApp
FROM
users_data
GROUP BY hasAnyApp;
-- Uses Android app
SELECT
COUNT(hasAndroidApp), hasAndroidApp
FROM
users_data
GROUP BY hasAndroidApp;
-- Uses ios app
SELECT
COUNT(hasIosApp), hasIosApp
FROM
users_data
GROUP BY hasIosApp;
-- Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers. (Hint: consider only those users having at least 1 product bought.)
SELECT
country, COUNT(type) c
FROM
users_data
WHERE
productsbought >= 1
GROUP BY country
ORDER BY c DESC;
-- Calculate 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
country, COUNT(type) c
FROM
users_data
WHERE
productssold >= 1
GROUP BY country
ORDER BY c;
-- Display name of top 10 countries having maximum products pass rate
SELECT
country, MAX(productspassrate) p
FROM
users_data
GROUP BY country
ORDER BY p DESC
LIMIT 10;

-- Calculate the number of users on an ecommerce platform for different language choices.
Select language,count(type) 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.
-- (Hint: use UNION to answer this question.)
SELECT
'products wished', SUM(productswished) Count
FROM
users_data
WHERE
Gender = 'f'
UNION SELECT
'products liked', SUM(socialproductsliked) s
FROM
users_data
WHERE
Gender = 'f';
-- Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT
'Products Sold', COUNT(productssold)
FROM
users_data
WHERE
gender = 'm'
UNION SELECT
'Products bought', COUNT(productsbought)
FROM
users_data
WHERE
Gender = 'm';

-- Which country is having maximum number of buyers?
SELECT
country, COUNT(type) Total_buyers
FROM
users_data
GROUP BY country
ORDER BY Total_buyers DESC;

-- List the name of 10 countries having zero number of sellers.
SELECT
country, productssold
FROM
users_data
WHERE
productssold = 0
ORDER BY productssold
LIMIT 10;

-- Display record of top 110 users who have used ecommerce platform recently.
SELECT
*
FROM
users_data
ORDER BY dayssincelastlogin
LIMIT 110;

-- Calculate the number of female users those who have not logged in since last 100 days.
SELECT
Gender, COUNT(type) no_of_Female
FROM
users_data
WHERE
dayssincelastlogin > 100
AND gender = 'f';

-- Display the number of female users of each country at ecommerce platform.
SELECT
country, COUNT(gender) no_of_female
FROM
users_data
WHERE
gender = 'f'
GROUP BY country;

-- Display the number of male users of each country at ecommerce platform.
SELECT
country, COUNT(gender) no_of_male
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(productsbought)
FROM
users_data
WHERE
gender = 'm'
GROUP BY country;