Project 1

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

USE ecommerce;
DESC users_data;
SELECT * FROM ecommerce.users_data;
#______________________________________________________________________________________________
# 4.) Run SQL command to select first 100 rows of the database

SELECT * FROM ecommerce.users_data LIMIT 100;
#______________________________________________________________________________________________
# 5.) How many distinct values exist in table for field country and language

SELECT DISTINCT country , language FROM ecommerce.users_data;
#______________________________________________________________________________________________
# 6.) Check whether male users are having maximum followers or female users.

SELECT gender,SUM(socialNbFollowers), COUNT(gender) FROM ecommerce.users_data GROUP BY gender;
# FEMALE users having maximum followers

#______________________________________________________________________________________________
# 7.) 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
sum(CASE WHEN hasProfilePicture = 'True'THEN 1 ELSE 0 END) as Picture_Count,
sum(CASE WHEN hasAnyApp = 'True' THEN 1 ELSE 0 END) as AnyApp_Count,
sum(CASE WHEN hasAndroidApp = 'True' THEN 1 ELSE 0 END) as AndroidApp_Count,
sum(CASE WHEN hasIosApp = 'True' THEN 1 ELSE 0 END) as IosApp_Count
FROM
ecommerce.users_data;
#______________________________________________________________________________________________
# 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 SUM(productsBought) AS NUMBER_OF_BUYERS, country FROM ecommerce.users_data WHERE productsBought NOT LIKE '0'
GROUP BY country
ORDER BY SUM(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 SUM(productsSold) AS NUMBER_OF_SELLERS, country FROM ecommerce.users_data WHERE productsSold NOT LIKE '0'
GROUP BY country
ORDER BY SUM(productsSold) ASC;
#______________________________________________________________________________________________
# 10.) Display name of top 10 countries having maximum products pass rate.

SELECT SUM(productsPassRate) , country FROM ecommerce.users_data
WHERE productsPassRate NOT LIKE "0"
GROUP BY country ORDER BY SUM(productsPassRate) DESC LIMIT 10;

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

SELECT COUNT(type) AS number_of_users, language FROM ecommerce.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 gender, SUM(socialProductsLiked) AS 'Social Products Liked', SUM(productsWished) AS 'Products Wished'
FROM ecommerce.users_data GROUP BY gender
UNION SELECT 'Total', SUM(socialProductsLiked), SUM(productsWished) FROM ecommerce.users_data;

# choice of female users about putting the product in a to like socially
#______________________________________________________________________________________________
# 13.) Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)

SELECT gender, SUM(productsSold) , SUM(productsBought)
FROM ecommerce.users_data GROUP BY gender
UNION SELECT "Total", SUM(productsSold), SUM(productsBought) FROM ecommerce.users_data;

# choice of male users about being buyer

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

SELECT country, SUM(productsBought) AS number_of_buyers, SUM(productsSold) AS Sellers
FROM ecommerce.users_data GROUP BY country ORDER BY SUM(productsBought) DESC;
#______________________________________________________________________________________________
# 15.) List the name of 10 countries having zero number of sellers.

SELECT country, productsSold
FROM ecommerce.users_data WHERE productsSold = "0" GROUP BY country LIMIT 10;

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

SELECT identifierHash , type , daysSinceLastLogin FROM ecommerce.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 type, COUNT(gender) FROM ecommerce.users_data
WHERE gender ="F" AND daysSinceLastLogin >= 100 ;

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

SELECT type, country, gender, COUNT(gender) FROM ecommerce.users_data
WHERE gender ="F" GROUP BY country ORDER BY COUNT(gender) DESC;
#______________________________________________________________________________________________
# 19.) Display the number of male users of each country at ecommerce platform.

SELECT type, country, gender, COUNT(gender) FROM ecommerce.users_data
WHERE gender ="M" GROUP BY country ORDER BY COUNT(gender) DESC;
#______________________________________________________________________________________________
# 20.) Calculate the average number of products sold and bought on ecommerce platform by male users for each country.

SELECT country, gender, AVG(productsSold), AVG(productsBought) FROM ecommerce.users_data
WHERE gender ="M" GROUP BY country ORDER BY AVG(productsSold) DESC;