Project 1

Question 1.

CREATE DATABASE ecommerce;

Question 2.

Import .csv file users_data into MySQL

Question 3.

use ecommerce;
desc users_data;

Question 4.

SELECT * FROM users_data
LIMIT 100;

Question 5.

SELECT COUNT(DISTINCT country) as distinct_country,
COUNT(DISTINCT language) as distinct_language
FROM users_data;

Question 6.

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

Question 7.

-- (a)uses the profile picture in their profile
SELECT COUNT(*) users_has_profile_picture
FROM users_data
WHERE hasProfilePicture="True";

-- (b)uses application for ecommerce platform
SELECT COUNT(*) ecommerce_app_users
FROM users_data
WHERE hasAnyApp="True";

-- (c)uses the android app
SELECT COUNT(*) android_app_users
FROM users_data
WHERE hasAndroidApp="True";

-- (d)uses ios app
SELECT COUNT(*) ios_app_users
FROM users_data
WHERE hasIosApp="True";

Question 8.

SELECT country,
COUNT(productsBought) Total_Buyers
FROM users_data
WHERE productsBought>0
GROUP BY country
ORDER BY COUNT(productsBought) DESC;

Question 9.

SELECT country,
COUNT(productsSold) Total_Sellers
FROM users_data
WHERE productsSold >0
GROUP BY country
ORDER BY COUNT(productsSold) ASC;

Question 10.

SELECT country,COUNT(productsPassRate)
FROM users_data
WHERE productsPassRate >0
GROUP BY country
ORDER BY COUNT(productsPassRate)
DESC LIMIT 10;

Question 11.

SELECT COUNT(*) users_language
FROM users_data
GROUP BY language;

Question 12.

SELECT 'Products social Liked' Description ,
SUM(socialProductsLiked) Number_of_units
FROM users_data
WHERE gender='F'
UNION
SELECT 'Products Wish Listed',
SUM(productsWished)
FROM users_data
WHERE gender='F'

Question 13.

SELECT 'Sellers(Male)' Description ,
COUNT(productsListed) Number_of_Peoples
FROM users_data
WHERE gender='M' AND productsListed > 0
UNION
SELECT 'Buyers(Male)',
COUNT(productsBought)
FROM users_data
WHERE gender='M' AND productsBought > 0;

Question 14.

SELECT country,COUNT(productsBought) AS Buyers
FROM users_data
WHERE productsBought > 0
GROUP BY country
ORDER BY Buyers DESC LIMIT 1 ;

Question 15.

SELECT country,SUM(productsListed) AS Sellers
FROM users_data
GROUP BY country
ORDER BY Sellers
LIMIT 10;

Question 16.

SELECT identifierHash,daysSinceLastLogin AS recent_users
FROM users_data
ORDER BY recent_users
LIMIT 110;

Question 17.

SELECT COUNT(identifierHash) as female_users
FROM users_data
WHERE gender='F' AND daysSinceLastLogin > 100;

Question 18.

SELECT country,COUNT(identifierHash) as Female_users
FROM users_data
WHERE gender = 'F'
GROUP BY country;

Question 19.

SELECT country,COUNT(identifierHash) AS male_users
FROM users_data
WHERE gender ='M'
GROUP BY country;

Question 20.

SELECT country,AVG(productsSold),AVG(productsBought)
FROM users_data
WHERE gender = 'M'
GROUP BY country;