Project 1

-- 1.Create new schema as ecommerce
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)
use ecommerce;

-- 3.Run SQL command to see the structure of table
SELECT
*
FROM
users_data;
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(DISTINCT language) Language
FROM
users_data;

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

-- 7.Calculate the total users those
-- a.Uses Profile Picture in their Profile
SELECT
COUNT(hasProfilePicture) Having_Profilepicture
FROM
users_data
WHERE
hasProfilePicture = 'True';

-- b.Uses Application for Ecommerce platform
SELECT
COUNT(hasAnyApp) Having_anyapp
FROM
users_data
WHERE
hasAnyApp = 'True';

-- c.Uses Android app
SELECT
COUNT(hasAndroidApp) AndroidApp_users
FROM
users_data
WHERE
hasAndroidApp = 'True';

-- d.Uses ios app
SELECT
COUNT(hasIosApp) IosApp_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(productsBought) Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY country
ORDER BY 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(productsSold) sellers
FROM
users_data
WHERE
productsSold > 0
GROUP BY country
ORDER BY sellers;

-- 10.Display name of top 10 countries having maximum products pass rate.
SELECT
country, COUNT(productsPassRate)
FROM
users_data
WHERE
productsPassRate > 0
GROUP BY country
ORDER BY COUNT(productsPassRate) DESC
LIMIT 10;

-- 11.Calculate the number of users on an ecommerce platform for different language choices.
SELECT DISTINCT
language, COUNT(language)
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
'like socially', COUNT(socialProductsLiked) choice
FROM
users_data
WHERE
socialProductsLiked > 0 AND gender = 'F'
UNION SELECT
'Product wishlist', COUNT(productsWished)
FROM
users_data
WHERE
productsWished > 0 AND gender = 'F';

-- 13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT
'Seller', COUNT(productsSold) Choice
FROM
users_data
WHERE
productsSold > 0 AND gender = 'M'
UNION SELECT
'Buyer', COUNT(productsBought) Buyer
FROM
users_data
WHERE
productsBought > 0 AND gender = 'M';

-- 14.Which country is having maximum number of buyers?
SELECT
country, COUNT(productsBought) country_maximum_no_of_Buyers
FROM
users_data
WHERE
productsBought > 0
GROUP BY country
ORDER BY country_maximum_no_of_Buyers DESC
LIMIT 1;

-- 15.List the name of 10 countries having zero number of sellers.
SELECT
country, productsSold sellers
FROM
users_data
WHERE
productsSold = 0
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(*) Female
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(gender) Female_users
FROM
users_data
WHERE
hasAnyApp = 'True' AND gender = 'F'
GROUP BY country;

-- 19.Display the number of male users of each country at ecommerce platform.
SELECT
country, COUNT(gender) Male_users
FROM
users_data
WHERE
hasAnyApp = 'True' AND 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) Average_sell,
AVG(productsBought) Average_buy
FROM
users_data
WHERE
hasAnyApp = 'True' AND gender = 'M'
GROUP BY country;