Project 1

-- Q.1) Creadted schema as ecommerce
-- Q.2) imported .csv file user_data into MySQL

-- Q.3) Run SQL command to see the structure of table
use ecommerce;
SELECT * FROM users_data;
DESC users_data;

-- Q.4) Run SQL command to select first 100 rows of the database
SELECT * FROM USERS_DATA LIMIT 100;

-- Q.5) How many distinct values exist in table for field country and language
SELECT COUNT(DISTINCT country) DISTINCT_Country, COUNT(DISTINCT language)
DISTINCT_language FROM users_data;

-- Q.6) Check whether male users are having maximum followers or female users
SELECT SUM(socialNBFollowers) SUMOFsocialNBFollowers,
gender FROM users_data GROUP BY gender;

-- Q.7) Calculate the total users those
-- a.Uses Profile Picture in their Profile
-- b.Uses Application for Ecommerce platform
-- c.Uses Android app
-- d.Uses ios app

-- a.Uses Profile Picture in their Profile
SELECT COUNT(*) TotalUsesProfilePic FROM users_data WHERE hasProfilePicture = 'TRUE';
-- b.Uses Application for Ecommerce platform
SELECT COUNT(*) TotalUseshasAnyApp FROM users_data WHERE hasAnyApp = 'TRUE';
-- c.Uses Android app
SELECT COUNT(*) TotalUseshasAndroidApp FROM users_data WHERE hasAndroidApp = 'TRUE';
-- d.Uses ios app
SELECT COUNT(*) TotalUseshasIosApp FROM users_data WHERE hasIosApp = 'TRUE';

-- Q.8) Calculate the total number of buyers for each country and
-- sort the result in descending order of total number of buyers
SELECT country, SUM(productsBought) total_buyers FROM users_data
GROUP BY country ORDER BY total_buyers DESC;

SELECT COUNT(productsBought) FROM users_data WHERE
productsBought > 0 group by country order by count(productsBought) ;

-- Q.9) Calculate the total number of sellers for each country and
-- sort the result in ascending order of total number of sellers
SELECT country, SUM(productsSold) total_sellers FROM users_data
GROUP BY country ORDER BY total_sellers ASC;

SELECT COUNT(productsSold) FROM users_data WHERE productsSold > 0;

-- Q.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;

SELECT DISTINCT country, productsPassRate FROM users_data
ORDER BY productsPassRate DESC LIMIT 10;

-- Q.11) Calculate the number of users on an ecommerce platform for different
-- language choices.
SELECT language, COUNT(*) FROM users_data GROUP BY language;

-- Q.12) Check the choice of female users about putting the product
-- in a wishlist or to like socially on an ecommerce platform.
SELECT
' putting produts in wishlist', COUNT(productsWished) choices FROM users_data WHERE
productsWished > 0 AND gender = 'F'
UNION
SELECT
'like socially', COUNT(socialProductsLiked) choices2 FROM users_data WHERE
socialProductsLiked > 0 AND gender = 'F';

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

-- Q.13) Check the choice of male users about being seller or buyer.
SELECT
'selling', COUNT(productsSold) choices FROM users_data WHERE
productsSold > 0 AND gender = 'M'
UNION
SELECT
'Buying', COUNT(ProductsBought) choices2 FROM users_data WHERE
ProductsBought > 0 AND gender = 'M';

SELECT gender,SUM(productsSold), SUM(ProductsBought)
FROM users_data where gender='M' AND hasAnyApp='True';

-- Q.14) Which country is having maximum number of buyers?
SELECT country,MAX(productsBought) buyer FROM users_data GROUP BY country
ORDER BY MAX(productsBought) DESC LIMIT 1;

SELECT country,COUNT(productsBought) buyer FROM users_data
GROUP BY country ORDER BY COUNT(productsBought) DESC LIMIT 1;

-- Q.15) List the name of 10 countries having zero number of sellers
SELECT productsSold, country FROM users_data WHERE productsSold = 0 LIMIT 10;

-- Q.16) Display record of top 110 users who have used ecommerce platform recently.
SELECT * FROM users_data ORDER BY daysSinceLastLogin ASC LIMIT 110 ;

SELECT daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin ASC LIMIT 110;

-- Q.17) Calculate the number of female users those who have not logged in
-- since last 100 days
SELECT gender,COUNT(daysSinceLastLogin) daysSinceLastLogin FROM users_data
WHERE gender ='F' AND daysSinceLastLogin>100;

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

-- Q.18) Display the number of female users of each country at ecommerce platform.
SELECT country, COUNT(gender) Female FROM users_data WHERE
gender = 'F' AND hasAnyApp='True' GROUP BY country;

SELECT country, COUNT(gender) female FROM users_data WHERE
gender = 'F' AND hasAnyApp='True' GROUP BY country;

-- Q.19) Q.18) Display the number of male users of each country at ecommerce platform.
SELECT country, COUNT(gender) male FROM users_data WHERE
gender = 'M' AND hasAnyApp='True' GROUP BY country;

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

-- Q.20) Calculate the average number of products sold and bought on
-- ecommerce platform by male users for each country.
select avg(productsSold),avg(productsBought),gender,country from
users_data where gender='M' group by country;