Project 1

-- Q 1.
CREATE SCHEMA ecommerce;

/* Q 2.
(right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next -> choose options : Create a new table , select delete if exist -> next -> next) */
-- Done
use ecommerce;
select count(*) from users_data;

-- Q 3.
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'users_data';

-- Q 4.
USE ecommerce;
SELECT * FROM users_data LIMIT 100;
-- Q 5.

SELECT COUNT(DISTINCT country) country, COUNT(DISTINCT language) language
FROM users_data;

-- Q 6.
USE ecommerce;
SELECT SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;

-- Q 7.
-- a. Uses Profile Picture in their Profile
SELECT COUNT(*) hasProfilePicture FROM users_data WHERE hasProfilePicture='True';

-- b. Uses Application for Ecommerce platform
SELECT COUNT(*) hasAnyApp FROM users_data WHERE hasAnyApp='True';

-- c. Uses Android app
SELECT COUNT(*) hasAndroidApp FROM users_data WHERE hasAndroidApp='True';

-- d. Uses ios app
SELECT COUNT(*) hasIosApp FROM users_data WHERE hasIosApp='True';

-- Q 8.

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

-- Q 9.

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

-- Q 10.
SELECT country,SUM(productsPassRate) FROM users_data GROUP BY country ORDER BY SUM(productsPassRate) DESC LIMIT 10;

-- Q 11.

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

-- Q 12.

SELECT SUM(productsWished),SUM(socialProductsLiked) FROM users_data WHERE gender='F';

-- Q 13.

SELECT SUM(productsSold),SUM(productsBought) FROM users_data WHERE gender='M';

-- Q 14.

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

-- Q 15.

SELECT productsSold,country FROM users_data WHERE productsSold=0 LIMIT 10;

-- Q 16.

SELECT daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin ASC LIMIT 110;

-- OR

SELECT * FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

-- Q 17.

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

-- Q 18.

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

-- Q 19.

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

-- Q20.

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