Project 1

-- Task 1
# Created a schema "ecommerce"
USE ecommerce;

-- Task 2
# Imported the data from .csv file which contains 98913 rows and 27 columns
SELECT COUNT(*) FROM users_data;

-- Task 3
# Structure of table
DESC users_data;

-- Task 4
# To display first 100 rows of the database
SELECT * FROM users_data LIMIT 100;

-- Task 5
# Distinct values existing in fields country and language
SELECT DISTINCT country, language from users_data; # values
SELECT DISTINCT count(country), count(language) from users_data; # counted value

-- Task 6
# Male or Female users having max followers
SELECT gender, SUM(socialNbFollowers) Followers From users_data GROUP BY gender;

-- Task 7
SELECT
(SELECT COUNT(hasProfilePicture) from users_data where hasProfilePicture='TRUE') AS Profile_picture,
(SELECT COUNT(hasAnyAPP) from users_data where hasAnyAPP='TRUE') AS App_ecommerce,
(SELECT COUNT(hasAndroidApp) from users_data where hasAndroidApp='TRUE') AS Adroid_app,
(SELECT COUNT(hasIosApp) from users_data where hasIosApp='TRUE') As Ios_app;

-- Task 8
select SUM(productsBought) Total_Buyers, country FROM users_data where productsBought>0
GROUP BY country ORDER BY SUM(productsBought) DESC;

-- Task 9
select SUM(productsSold) Total_Sellers, country FROM users_data where productsSold>0
GROUP BY country ORDER BY SUM(productsSold);

-- Task 10
SELECT SUM(productsPassRate) Productspassrate, country FROM users_data WHERE productsPassRate>0
Group BY country ORDER BY SUM(productsPassRate) DESC LIMIT 10;

-- Task 11
SELECT COUNT(type) Total_users, language FROM users_data WHERE hasAnyApp='True'
group by language;

-- Task 12
SELECT count(*) As Female_Users, "Products Wished" As Choice FROM users_data where gender='F' AND productsWished>0 AND hasAnyApp='True'
UNION
SELECT count(*), "Socially Products Liked" FROM users_data where gender='F' AND socialProductsLiked>0 AND hasAnyApp='True';

-- Task 13
SELECT 'Seller' AS Choice, COUNT(*) AS Count_of_Male_users FROM users_data
WHERE gender = 'M' AND productsSold >0
UNION
SELECT 'Buyer' , COUNT(*) FROM users_data
WHERE gender = 'M' AND productsBought >0;

-- Task14
SELECT SUM(productsBought) As Maximum_Buyers, country FROM users_data
where productsBought>0 Group BY country ORDER BY SUM(productsBought) DESC limit 1;

-- Task15
SELECT country from users_data where productsSold=0 GROUP BY country LIMIT 10;

-- Task16
select identifierHash, daysSinceLastLogin from users_data WHERE hasAnyApp = 'True' ORDER BY daysSinceLastLogin LIMIT 110 ;

-- Task17
SELECT count(*) No_female_users from users_data where gender='F' AND daysSinceLastLogin > 100 ;

-- Task18
SELECT count(*) No_female_users, country from users_data
where gender='F' AND hasAnyApp = 'True'
group by country;

-- Task19
SELECT count(*) No_male_users, country from users_data
where gender='M' AND hasAnyApp = 'True'
group by country;

-- Task20
SELECT AVG(productsSold) Avg_products_sold, AVG(productsBought) Avg_products_bought,
country from users_data
where hasAnyApp = 'True' AND gender='M' AND productsSold>0 AND productsBought>0
GROUP BY country;