Project 1

## 1.Create new schema as ecommerce
CREATE SCHEMA 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)

## 3.Run SQL command to see the structure of table 
DESCRIBE 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 COUNT(DISTINCT country) AS distinct_country_count
FROM ecommerce.users_data;

## 6.Check whether male users are having maximum followers or female users.
SELECT gender, SUM(gender) AS total_followers
FROM ecommerce.users_data
GROUP BY gender
ORDER BY total_followers DESC
LIMIT 1;

## 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 COUNT(*) AS total_users_with_profile_picture
FROM ecommerce.users_data
WHERE hasProfilePicture = 'Yes';
SELECT COUNT(*) AS total_users_ecommerce_app
FROM ecommerce.users_data
WHERE hasAnyApp = 'Yes';
SELECT COUNT(*) AS total_users_android_app
FROM ecommerce.users_data
WHERE hasAndroidApp = 'Yes';
SELECT COUNT(*) AS total_users_ios_app
FROM ecommerce.users_data
WHERE hasIosApp = 'Yes';

## 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(*) AS total_buyers
FROM ecommerce.users_data
WHERE productsBought = 'Yes'
GROUP BY country
HAVING total_buyers > 0
ORDER BY total_buyers DESC;

SELECT * FROM ecommerce.users_data LIMIT 100;

## 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(*) AS total_sellers
FROM ecommerce.users_data
WHERE productsSold = 'Yes'
GROUP BY country
HAVING total_sellers > 0
ORDER BY total_sellers ASC;

## 10.Display name of top 10 countries having maximum products pass rate.
SELECT country, 
       SUM(productsPassRate) / COUNT(*) AS products_pass_rate
FROM ecommerce.users_data
GROUP BY country
ORDER BY products_pass_rate DESC
LIMIT 10;

## 11.Calculate the number of users on an ecommerce platform for different language choices.
SELECT language, COUNT(*) AS user_count
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 'productsWished' AS choice, productsWished AS decision, COUNT(*) AS count
FROM ecommerce.users_data
WHERE gender = 'f'
GROUP BY productsWished
UNION
SELECT 'socialProductsLiked' AS choice, socialProductsLiked AS decision, COUNT(*) AS count
FROM ecommerce.users_data
WHERE gender = 'f'
GROUP BY socialProductsLiked;

SELECT * FROM ecommerce.users_data LIMIT 100;

## 13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.) 
SELECT 'productsSold' AS choice, productsSold AS decision, COUNT(*) AS count
FROM ecommerce.users_data
WHERE gender = 'm'
GROUP BY productsSold
UNION
SELECT 'productsBought' AS choice, productsBought AS decision, COUNT(*) AS count
FROM ecommerce.users_data
WHERE gender = 'm'
GROUP BY productsBought;

## 14.Which country is having maximum number of buyers?
SELECT country, COUNT(*) AS total_buyers
FROM ecommerce.users_data
WHERE productsBought = 'Yes'
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;

## 15.List the name of 10 countries having zero number of sellers.
SELECT country
FROM ecommerce.users_data
WHERE productsSold = 'No'
GROUP BY country
HAVING COUNT(*) = 0
LIMIT 10;

## 16.Display record of top 110 users who have used ecommerce platform recently.
SELECT *
FROM ecommerce.users_data
ORDER BY daysSinceLastLogin DESC
LIMIT 110;

## 17.Calculate the number of female users those who have not logged in since last 100 days.
SELECT COUNT(*) AS female_users_inactive
FROM ecommerce.users_data
WHERE gender = 'f' AND daysSinceLastLogin <= NOW() - INTERVAL 100 DAY;

## 18.Display the number of female users of each country at ecommerce platform.
SELECT country, COUNT(*) AS female_user_count
FROM ecommerce.users_data
WHERE gender = 'f'
GROUP BY country;

## 19.Display the number of male users of each country at ecommerce platform.
SELECT country, COUNT(*) AS male_user_count
FROM ecommerce.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,
       AVG(productsSold) AS avg_products_sold,
       AVG(productsBought) AS avg_products_bought
FROM ecommerce.users_data
WHERE gender = 'm'
GROUP BY country;