Project 1

Business model Customer to Customer

-- 1. Create new schema as ecommerce
CREATE DATABASE ecommerce;
-- 2.Import .csv file users_data into MySQL
use ecommerce;
-- 3.Run SQL command to see the structure of table
DESC users_data;
-- 4.Run SQL command to select first 100 rows of the database
SELECT
*
FROM
users_data
LIMIT 100;
-- 5. How many distinct values exist in table for field country and language
SELECT
COUNT(DISTINCT country) country_count,
COUNT(DISTINCT language) language_count
FROM
users_data;
-- 6.Check whether male users are having maximum followers or female users.
SELECT
gender, SUM(socialNbFollowers) Followers
FROM
users_data
GROUP BY GENDER;
-- 7.Calculate the total users those
-- Uses Profile Picture in their Profile
-- Uses Application for Ecommerce platform
-- Uses Android app
-- Uses ios app
SELECT
COUNT(*)
FROM
users_data
WHERE
hasProfilePicture = 'TRUE';
SELECT
COUNT(*)
FROM
users_data
WHERE
hasAnyApp = 'TRUE';
SELECT
COUNT(*)
FROM
users_data
WHERE
hasAndroidApp = 'TRUE';
SELECT
COUNT(*)
FROM
users_data
WHERE
hasIosApp = 'TRUE';
-- 8.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(productsBought) products_Bought
FROM
users_data
WHERE
ProductsBought >= 1
GROUP BY country
ORDER BY COUNT(productsBought) DESC;
-- 9.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(ProductsSold) Average_productsSold
FROM
users_data
WHERE
ProductsSold >= 1
GROUP BY country
ORDER BY COUNT(ProductsSold) ASC;
-- 10.Display name of top 10 countries having maximum products pass rate
SELECT
country, COUNT(productsPassRate) AS TOP_TEN
FROM
users_data
WHERE
productsPassRate > 0
GROUP BY country
ORDER BY TOP_TEN DESC
LIMIT 10;
-- 11 Calculate the number of users on an ecommerce platform for different language choices.
SELECT
Language, COUNT(*)
FROM
users_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. (Hint: use UNION to answer this question.)
SELECT 'Description', 'No. of units'
UNION SELECT
'Products Social Liked', SUM(socialProductsLiked)
FROM
users_data
WHERE
gender = 'F'
UNION SELECT
'Products wishlisted', SUM(productsWished) wishlisted
FROM
users_data
WHERE
gender = 'F';
-- 13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT 'Description', 'No. of people'
UNION SELECT
'Sellers(Male)', COUNT(productssold)
FROM
users_data
WHERE
gender = 'M' AND ProductsSold>0
UNION SELECT
'Buyers(Male)', COUNT(productsBought)
FROM
users_data
WHERE
gender = 'M' AND productsBought>0 ;
-- 14 Which country is having maximum number of buyers?
SELECT
country, COUNT(productsBought) AS Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY
country
ORDER BY
Buyers DESC LIMIT 1;
-- 15 List the name of 10 countries having zero number of sellers.
SELECT
country, SUM(productsListed) List
FROM
users_data
GROUP BY country
ORDER BY List
LIMIT 10;
-- 16 Display record of top 110 users who have used ecommerce platform recently.
SELECT
daysSinceLastLogin
FROM
users_data
ORDER BY daysSinceLastLogin ASC
LIMIT 110;
-- 17. Calculate the number of female users those who have not logged in since last 100 days.
SELECT
COUNT(gender) NO_OF_FEMALES
FROM
users_data
WHERE
gender = 'f'
AND daysSinceLastLogin > 100;
-- 18 Display the number of female users of each country at ecommerce platform.
SELECT
country, COUNT(gender)
FROM
users_data
WHERE
gender = 'F'
GROUP BY country;
-- 19 Display the number of male users of each country at ecommerce platform
SELECT
country, COUNT(gender)
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
-- 20 Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT
country,
COUNT(ProductsSold) PRODUCTS_SOLD,
COUNT(ProductsBought) PRODUCTS_BOUGHT
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;