Project 1
-- Q1. Create new schema as ecommerce
CREATE DATABASE ecommerce;
/* Q2. Import .csv file users_data into MySQL.
(right click on ecommerce schema -> Table Data import Wizard -> Give path of the file ->
ext -> choose options : Create a new table , select delete if exist -> next -> next)
*/
SELECT *
FROM users_data;
-- Q3. Run SQL command to see the structure of table
DESCRIBE users_data;
-- Q4. Run SQL command to select first 100 rows of the database
SELECT *
FROM users_data
LIMIT 100;
-- Q5. How many distinct values exist in table for field country and language
SELECT count(distinct(country)) as distinct_countries,
count(distinct(language)) as distinct_languages
FROM users_data;
-- Q6. Check whether male users are having maximum followers or female users.
SELECT gender, sum(socialNbFollowers)
FROM users_data
GROUP BY gender;
/*
Q7. Calculate the total users those
a. Uses Profile Picture in their Profile
b. Uses Application for Ecommerce platform
c. Uses Android app
d. Uses ios app
*/
SELECT 'total_users_PP' as users, count(hasProfilePicture) as count
FROM users_data
WHERE hasProfilePicture IN ('true')
UNION
SELECT 'total_users_Ecommerce' as users, count(hasAnyApp) as count
FROM users_data
WHERE hasAnyApp = 'true'
UNION
SELECT 'total_users_Android' as users, count(hasAndroidApp) as count
FROM users_data
WHERE hasAndroidApp = 'true'
UNION
SELECT 'total_users_IOS' as users, count(hasIosApp) as count
FROM users_data
WHERE hasIosApp = 'true'
;
-- Q8. 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, sum(productsBought) as num_of_buyers
FROM users_data
WHERE productsBought >= 1
GROUP BY country
ORDER BY num_of_buyers DESC
;
-- Q9. 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, sum(productsSold) as num_of_sellers
FROM users_data
WHERE productsSold >= 1
GROUP BY country
ORDER BY num_of_sellers ASC
;
-- Q10.Display name of top 10 countries having maximum products pass rate.
SELECT country, sum(productsPassRate) as pass_rate
FROM users_data
WHERE productsPassRate >= 0
GROUP BY country
ORDER BY pass_rate DESC
LIMIT 10;
-- Q11.Calculate the number of users on an ecommerce platform for different language choices.
SELECT language, count(identifierHash) as users
FROM users_data
GROUP BY language;
-- Q12.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 'Female' as gender, 'products_wishlisted' as type , sum(productsWished) as sum
FROM users_data
WHERE productsWished > 0 AND gender = 'F'
UNION
SELECT 'Female', 'social_prod_liked', sum(socialProductsLiked)
FROM users_data
WHERE socialProductsLiked > 0 AND gender = 'F';
-- Q13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT 'Male' as gender, 'seller' as type , sum(productsSold) as total
FROM users_data
WHERE productsSold > 0 AND gender = 'M'
UNION
SELECT 'Male', 'buyer', sum(productsBought)
FROM users_data
WHERE productsBought > 0 AND gender = 'M';
-- Q14.Which country is having maximum number of buyers?
SELECT country, sum(productsBought) as products_bought
FROM users_data
GROUP BY country
ORDER BY products_bought DESC
LIMIT 1;
-- Q15.List the name of 10 countries having zero number of sellers.
SELECT country, sum(productsSold) as total_sellers
FROM users_data
GROUP BY country
ORDER BY total_sellers
LIMIT 10;
-- Q16.Display record of top 110 users who have used ecommerce platform recently.
SELECT identifierHash as user, daysSinceLastLogin as last_login_in_days
FROM users_data
ORDER BY last_login_in_days ASC
LIMIT 110;
-- Q17.Calculate the number of female users those who have not logged in since last 100 days.
SELECT 'female users not logged in since last 100 days' as login_details, count(identifierHash) no_of_users
FROM users_data
WHERE gender = 'F' AND daysSinceLastLogin > 100;
-- Q18.Display the number of female users of each country at ecommerce platform
SELECT country, count(identifierHash)
FROM users_data
WHERE gender = 'F'
GROUP BY country;
-- Q19.Display the number of male users of each country at ecommerce platform.
SELECT country, count(identifierHash)
FROM users_data
WHERE gender = 'M'
GROUP BY country;
-- Q20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT country, count(identifierHash) as no_of_male_users, avg(productsSold) as avg_prod_sold, avg(productsBought) as avg_prod_bought
FROM users_data
WHERE gender = 'M'
GROUP BY country;