Project
-- 1
CREATE DATABASE ecommerce;
USE ecommerce;
-- 3
DESC users_data;
-- 4
SELECT * FROM users_data LIMIT 100;
-- 5
SELECT COUNT(DISTINCT country) distinct_country,
COUNT(DISTINCT language) distinct_language
FROM users_data;
-- 6
SELECT gender,SUM(socialNbFollowers) AS 'Sum of Followers' FROM users_data GROUP BY gender;
-- 7
a)
SELECT 'Uses Profile Picture' Value, COUNT(hasProfilePicture) AS 'Total users' FROM users_data WHERE hasProfilePicture = 'true';
b)
SELECT 'Uses Ecommerce platform' Value, COUNT(hasAnyApp) AS 'Total users' FROM users_data WHERE hasAnyApp = 'true';
c)
SELECT 'Uses Android app' Value, COUNT(hasAndroidApp) AS 'Total users' FROM users_data WHERE hasAndroidApp = 'true';
d)
SELECT 'Uses ios app' Value, COUNT(hasIosApp) AS 'Total users' FROM users_data WHERE hasIosApp = 'true';
-- 8
SELECT country, COUNT(productsBought) AS 'Total number of buyers' FROM users_data
WHERE productsBought > 0
GROUP BY country
ORDER BY COUNT(productsBought) DESC;
-- 9
SELECT country, COUNT(productsSold) AS 'Total number of Sellers' FROM users_data
WHERE productsSold > 0
GROUP BY country
ORDER BY COUNT(productsSold);
-- 10
SELECT DISTINCT country AS 'Top 10 countries having maximum products pass rate' FROM users_data
GROUP BY country
ORDER BY(SUM(productsPassRate))DESC LIMIT 10;
-- 11
SELECT
language, COUNT(*) Users
FROM
users_data
GROUP BY language;
-- 12
SELECT 'putting the product in a wishlist' AS 'choice of female users', SUM(productsWished) AS 'Total users' FROM users_data WHERE gender='F'
UNION
SELECT 'like socially on an ecommerce platform', SUM(socialProductsLiked) FROM users_data WHERE gender='F';
-- 13
SELECT 'Seller' AS 'choice of male users', SUM(productsSold) AS 'Total users' FROM users_data WHERE gender='M'
UNION
SELECT 'Buyer', SUM(productsBought) FROM users_data WHERE gender='M';
-- 14
SELECT country AS 'Country has maximum buyers' FROM users_data
GROUP BY country
ORDER BY SUM(productsBought) DESC LIMIT 1;
-- 15
SELECT country, SUM(productsSold) FROM users_data
GROUP BY country
ORDER BY SUM(productsSold)
LIMIT 10;
-- 16
SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;
-- 17
SELECT COUNT(daysSinceLastLogin) AS 'female users those who have not logged in since last 100 days'
FROM users_data
WHERE gender='F' AND daysSinceLastLogin >=100
GROUP BY gender;
-- 18
SELECT country, COUNT(gender) AS 'number of female users'
FROM users_data
WHERE gender='F' AND hasAnyApp = 'true'
GROUP BY country;
-- 19
SELECT country, COUNT(gender) AS 'number of male users'
FROM users_data
WHERE gender='M' AND hasAnyApp = 'true'
GROUP BY country;
-- 20
SELECT gender, country,
AVG(productsSold) AS 'average number of products sold',
AVG(productsBought) AS 'average number of products bought'
FROM users_data
WHERE gender='M' AND hasAnyApp = 'true'
GROUP BY country;