Project 1

CREATE DATABASE IF NOT EXISTS ecommerce;
USE ecommerce;
-- 3 Run SQL command to see the structure of table

DESCRIBE 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) AS distinct_countries,
COUNT(DISTINCT language) AS distinct_languages
FROM users_data;

-- 6 Check whether male users are having maximum followers or female users.

SELECT
gender,
SUM(socialNbFollowers) AS total_followers
FROM users_data
GROUP BY gender
ORDER BY total_followers DESC;

-- 7 Calculate the total users those use Profile Picture, Application, Android app, and iOS app

-- Option 1
SELECT
COUNT(CASE WHEN hasProfilePicture = 'TRUE' THEN 1 END) AS total_users_with_profile_picture,
COUNT(CASE WHEN hasAnyApp = 'TRUE' THEN 1 END) AS total_users_using_ecommerce,
COUNT(CASE WHEN hasAndroidApp = 'TRUE' THEN 1 END) AS total_users_using_android,
COUNT(CASE WHEN hasIosApp = 'TRUE' THEN 1 END) AS total_users_using_ios
FROM users_data;

-- Option 2
SELECT COUNT(*) AS total_users_with_profile_picture
FROM users_data
WHERE hasProfilePicture ='TRUE';

SELECT COUNT(*) AS total_users_using_ecommerce
FROM users_data
WHERE hasAnyApp = 'TRUE';

SELECT COUNT(*) AS total_users_using_android
FROM users_data
WHERE hasAndroidApp = 'TRUE';

SELECT COUNT(*) AS total_users_using_ios
FROM users_data
WHERE hasIosApp = 'TRUE';

-- 8 Calculate the total number of buyers for each country

SELECT
country,
COUNT(*) AS total_buyers
FROM users_data
WHERE productsBought >= 1
GROUP BY country
ORDER BY total_buyers DESC;

-- 9 Calculate the total number of sellers for each country

SELECT
country,
COUNT(*) AS total_sellers
FROM users_data
WHERE productsSold >= 1
GROUP BY country
ORDER BY total_sellers;

-- 11 Calculate the number of users on an ecommerce platform for different language choices

SELECT
language,
COUNT(*) AS total_users
FROM users_data
GROUP BY language
ORDER BY total_users DESC;

-- 12 Check the choice of female users about wishlist or social likes

SELECT
'productsWished' AS choice,
COUNT(*) AS total_users
FROM users_data
WHERE gender = 'F' AND productsWished >= 1

UNION

SELECT
'socialProductsLiked' AS choice,
COUNT(*) AS total_users
FROM users_data
WHERE gender = 'F' AND socialProductsLiked >= 1;

-- 13 Check the choice of male users about being seller or buyer

SELECT
'productsSold' AS role,
COUNT(*) AS total_users
FROM users_data
WHERE gender = 'M' AND productsSold >= 1

UNION

SELECT
'productsBought' AS role,
COUNT(*) AS total_users
FROM users_data
WHERE gender = 'M' AND productsBought >= 1;

-- 14 Which country is having maximum number of buyers?

SELECT
country,
COUNT(*) AS total_buyers
FROM users_data
WHERE productsbought >= 1
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;

-- 15 List the name of 10 countries having zero sellers

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

-- 16 Display record of top 110 users who have used ecommerce platform recently

SELECT
country,
COUNT(*) AS Newer_Users,
MIN(seniorityAsYears) AS seniority
FROM users_data
GROUP BY country
ORDER BY seniority ASC
LIMIT 110;

-- 17 Calculate the number of female users who have not logged in since last 100 days

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

-- 18 Display the number of female users of each country

SELECT
country,
COUNT(*) AS Ecommers_Female_users
FROM users_data
WHERE gender = 'F'
AND hasAnyApp='True'
GROUP BY country;

-- 19 Display the number of male users of each country

SELECT
country,
COUNT(*) AS Ecommers_Male_users
FROM users_data
WHERE gender = 'M'
AND hasAnyApp='True'
GROUP BY country;

-- 20 Calculate the average number of products sold and bought by male users for each country

SELECT
country,
AVG(productsSold) AS avg_products_sold,
AVG(productsBought) AS avg_products_bought
FROM users_data
WHERE gender = 'M'
AND hasAnyApp = 'True'
AND productsBought >= 1
AND productsSold >= 1
GROUP BY country;