Project 1

-- 1
create database ecommerce;
use ecommerce;
-- 3
desc users_data;
-- 4
SELECT
*
FROM
users_data
LIMIT 100;
-- 5
SELECT
count( DISTINCT country, language)
FROM
users_data;
-- 6
SELECT
SUM(socialNbFollowers), gender
FROM
users_data
GROUP BY gender;
-- 7a
SELECT
COUNT(*)
FROM
users_data
WHERE
hasProfilePicture = 'true';

SELECT DISTINCT
COUNT(identifierHash)
FROM
users_data
WHERE
hasProfilePicture = 'true';
-- 7b
SELECT DISTINCT
COUNT(identifierHash)
FROM
users_data
WHERE
hasAnyApp = 'true';
-- 7c
SELECT DISTINCT
COUNT(identifierHash)
FROM
users_data
WHERE
hasAndroidApp = 'true';
use ecommerce;

/* 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
COUNT(identifierHash) total_buyers,
country,
SUM(productsBought)
FROM
users_data
WHERE
productsBought >= 1
GROUP BY country
ORDER BY total_buyers DESC;
/*
9Calculate 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 DISTINCT
COUNT(identifierHash) total_sellers,
country,
SUM(productsSold)
FROM
users_data
WHERE
productsSold >= 1
GROUP BY country
ORDER BY total_sellers ASC;

/*10 Display name of top 10 countries having maximum products pass rate.*/
SELECT
country, SUM(productspassrate)
FROM
users_data
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
*
FROM
users_data;
SELECT
COUNT(identifierHash), language
FROM
users_data
WHERE
productsBought >= 1
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
'products listed', SUM(productsListed)
FROM
users_data
WHERE
gender = 'F'
UNION SELECT
'social Products Liked ', SUM(socialProductsLiked)
FROM
users_data
WHERE
gender = 'F';
/*
13 Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
*/

SELECT
'sellers', COUNT(identifierHash) total_sellers
FROM
users_data
WHERE
productsSold >= 1 AND GENDER = 'M'
UNION SELECT
'buyers', COUNT(identifierHash) total_buyers
FROM
users_data
WHERE
productsBought >= 1 AND GENDER = 'M';

/* 14 Which country is having maximum number of buyers?*/
SELECT
(COUNT(identifierHash)) total_users, country
FROM
users_data
WHERE
productsBought >= 1
GROUP BY country
ORDER BY total_users DESC
LIMIT 1;

/*15 List the name of 10 countries having zero number of sellers.*/
SELECT DISTINCT
country
FROM
users_data
WHERE
productsSold = 0
GROUP BY country;

/*
/*16 Display record of top 110 users who have used ecommerce platform recently.
17 Calculate the number of female users those who have not logged in since last 100 days.
18 Display the number of female users of each country at ecommerce platform.
19 Display the number of male users of each country at ecommerce platform.
20 Calculate the average number of products sold and bought on ecommerce platform by male users for each country.*/
SELECT
*
FROM
users_data;
-- 16
SELECT
*
FROM
users_data
ORDER BY daysSinceLastLogin ASC
LIMIT 110;
-- 17
SELECT
COUNT(*)
FROM
users_data
WHERE
gender = 'F'
AND daysSinceLastLogin > 100;
-- 18
SELECT
COUNT(*), country
FROM
users_data
WHERE
gender = 'F'
GROUP BY country;
-- 19
SELECT
COUNT(*), country
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
-- 20
SELECT
AVG(productsBought), AVG(productsSold), country
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;