Project 1

--- Q3) Run SQL command to see the structure of table
USE ecommerce;
DESCRIBE users_data ;

--- Q4) Run SQL command to select first 100 rows of the database
SELECT * FROM users_data LIMIT 100 ;

--- Q5) ) How many distinct values exist in table for field country and language
SELECT DISTINCT country,language FROM users_data;

--- Q6) Check whether male users are having maximum followers or female users.
SELECT gender FROM users_data;

SELECT gender,
COUNT(CASE WHEN gender = 'M' THEN 1 END) AS " Male " ,
COUNT(CASE WHEN gender = 'F' THEN 1 END) AS " Female " ,
COUNT(*) AS Total_count
FROM users_data;

SELECT
gender, SUM(socialNbFollowers)
FROM
users_data
GROUP BY gender;

---- Q7) 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 * FROM users_data

SELECT hasProfilePicture,
COUNT(CASE WHEN hasProfilePicture = 'True' THEN 1 END) AS " True " ,
COUNT(CASE WHEN hasProfilePicture = 'False' THEN 1 END) AS " False " ,
COUNT(*) AS Total_count
FROM users_data;

SELECT SUM(hasAndroidApp) FROM users_data;

--- Q8) 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 * FROM users_data

SELECT country,
COUNT(productsBought)
FROM users_data
GROUP BY country
ORDER BY COUNT(country);

--- 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 * FROM users_data

SELECT country,
COUNT(productsSold)
FROM users_data
GROUP BY country
ORDER BY COUNT(country);

-- 10) Display name of top 10 countries having maximum products pass rate.
SELECT * FROM users_data;

SELECT country, count(country)
FROM users_data
GROUP BY country
ORDER BY country DESC
limit 10;

--- 11) Calculate the number of users on an ecommerce platform for different language choices.
SELECT * FROM users_data;
SELECT
COUNT(language)
FROM
users_data;

--- 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 * FROM users_data;

SELECT
' putting produts in wishlist',
COUNT(productsWished) choices
FROM
users_data
WHERE
productsWished > 0 AND gender = 'F'
UNION SELECT
'like socially', COUNT(socialProductsLiked) choices2
FROM
users_data
WHERE
socialProductsLiked > 0 AND gender = 'F';

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

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

SELECT
country,count(productsBought)
FROM
users_data group by country order by count(productsBought) desc limit 1;

--- 15) List the name of 10 countries having zero number of sellers.
SELECT * FROM users_data;
SELECT country,productsSold 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;

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 * FROM users_data;
SELECT
COUNT(gender) female
FROM
users_data

WHERE
gender = 'F'
AND daysSinceLastLogin = 100;

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

SELECT
country, COUNT(gender) 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(gender) 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
AVG(productsSold) Sold,
AVG(productsBought) Bought
FROM
users_data
WHERE
gender = 'M' AND hasAnyApp = 'TRUE'
GROUP BY country;