Project 1

# 1) Create new schema as ecommerce
# Ans) schema can be created using navigator as shown in attachment or
# by using the below query

CREATE DATABASE ecommerce;

# 2) Import .csv file users_data into MySQL
# (right click on ecommerce schema -> Table Data import Wizard ->
# Give path of the file -> Next -> choose options : Create a new table ,
# select delete if exist -> next -> next)

# The answer is shown in the image attached

# 3) Run SQL command to see the structure of table

USE ecommerce;
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), COUNT(DISTINCT language) FROM users_data;

# 6) Check whether male users are having maximum followers or female users.
SELECT gender, SUM(socialNbFollowers) FROM users_data GROUP BY gender;

# from the table obtained it is clear that female users have maximum followers

# 7) Calculate the total users those
# a) Uses Profile Picture in their Profile
SELECT COUNT(hasProfilePicture) No_of_users FROM users_data WHERE hasProfilePicture = 'TRUE';

# b) Uses Application for Ecommerce platform
SELECT COUNT(hasAnyApp) No_of_users FROM users_data WHERE hasAnyApp = 'TRUE';

# c) Uses Android app
SELECT COUNT(hasAndroidApp) No_of_users FROM users_data WHERE hasAndroidApp = 'TRUE';

# d) Uses ios app
SELECT COUNT(hasIosApp) No_of_users 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(*) Total_Buyers FROM users_data
WHERE productsBought > 0
GROUP BY country
ORDER BY Total_Buyers 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(*) Total_Sellers FROM users_data
WHERE productsSold > 0
GROUP BY country
ORDER BY Total_Sellers;

# 10) Display name of top 10 countries having maximum products pass rate.

SELECT country, MAX(productsPassRate) Pass_rate FROM users_data
GROUP BY country
ORDER BY Pass_rate DESC
LIMIT 10;

# 11) Calculate the number of users
# on an ecommerce platform for different language choices.

SELECT language, COUNT(*) No_of_Users 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 'Wishlist', SUM(productsWished) choice_of_female_users FROM users_data
WHERE gender = 'F'
UNION
SELECT 'Socially liked', SUM(socialProductsLiked) FROM users_data WHERE gender = 'F';

# From the table it can be seen that female users preferred to
# socially like the product than adding it to wishlist

# 13) Check the choice of male users about being seller or buyer.
# (Hint: use UNION to solve this question.)

SELECT 'Sold',SUM(productsSold) Choice_of_male_users FROM users_data
WHERE gender = 'M'
UNION
SELECT 'Bought',SUM(productsBought) FROM users_data WHERE gender = 'M';

# 14) Which country is having maximum number of buyers?

SELECT country, COUNT(*) Total_Buyers FROM users_data
WHERE productsBought > 0 # Considering buyers who have bought at least 1 product
GROUP BY country
ORDER BY Total_Buyers DESC LIMIT 1;

# 15) List the name of 10 countries having zero number of sellers.

SELECT country, COUNT(*) FROM users_data
WHERE productsSold = 0 GROUP BY country LIMIT 10;

# 16) Display record of top 110 users who have used ecommerce platform recently.

SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

# 17) Calculate the number of female users those
# who have not logged in since last 100 days.

SELECT COUNT(*) FROM users_data WHERE daysSinceLastLogin >= 100 AND gender = 'F';

# 18) Display the number of female users of each country at ecommerce platform.

SELECT country, COUNT(*) Female_users FROM users_data
WHERE gender = 'F' AND hasAnyApp = 'TRUE'
GROUP BY country;

# 19) Display the number of male users of each country at ecommerce platform.

SELECT country, COUNT(*) Male_users FROM users_data
WHERE gender = 'M' AND hasAnyApp = 'TRUE'
GROUP BY country;

# 20) Calculate the average number of products sold and bought on ecommerce
# platform by male users for each country.

SELECT country, AVG(productsSold) Average_sales, AVG(productsBought) Average_buys
FROM users_data WHERE gender = 'M' GROUP BY country;