Project 1

Q1. Create new schema as ecommerce

create database ecommerce;

Q2. Import .csv file users_data into MySQL

use ecommerce;

Q3. Run SQL command to see the structure of table

DESC users_data;

Q4. Run SQL command to select first 100 rows of the database

SELECT * from users_data limit 100;

Q5. How many distinct values exist in table for field country and language?

SELECT COUNT(Distinct country) as Distinct_Countries, COUNT(DISTINCT language) as distinct_language from users_data;

Q6. Check whether male users are having maximum followers or female users.

SELECT gender,sum(socialNBFollowers) from users_data group by gender;

Q7. Calculate the total users those

A. Uses Profile Picture in their Profile - SELECT COUNT(hasProfilePicture) Total_Users_with_Profile_Pictures_in_their_Profile from users_data where hasprofilepicture="True";

B. Uses Application for Ecommerce platform-

SELECT COUNT(hasAnyApp) Total_Users_with_ApplicationForEcom_Platform from users_data where hasanyapp="True";

C. Uses Android app

SELECT COUNT(hasAndroidApp) Total_Users_with_Android_App from users_data where hasandroidapp="True";

D. Uses ios app

SELECT COUNT(hasIosApp) Total_Users_with_IOS_App from users_data where hasiosapp="True";

Q8. Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers.

SELECT country, COUNT(productsBought) as Total_Buyers from users_data group by country ORDER BY Total_Buyers DESC;

Q9. Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.

SELECT country, COUNT(productsSold) as Total_Sellers from users_data group by country ORDER BY Total_Sellers ASC;

Q10. Display name of top 10 countries having maximum products pass rate.

SELECT country from users_data group by country having max(productsPassRate) LIMIT 10;

Q11. Calculate the number of users on an ecommerce platform for different language choices.

SELECT language, COUNT(language) as language_spoken_by_users from users_data group by language;

Q12. Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform.

SELECT "Products_in_Wishlist",sum(productsWished) as Data from Users_data where Gender="F"
UNION
SELECT "Prodcts_Liked", sum(socialproductsliked) from Users_data where Gender="F";

Q13. Check the choice of male users about being seller or buyer.

SELECT "Products_Bought",sum(productsBought) as Total from Users_data where Gender="M"
UNION
SELECT "Prodcts_Sold", sum(productsSold) from Users_data where Gender="M";

Q14. Which country is having maximum number of buyers?

select country,max(productsBought) Max_no_of_buyers from users_data group by country order by max(productsBought) desc limit 1;

Q15. List the name of 10 countries having zero number of sellers.

SELECT country, MIN(productsSold) from users_data group by country order by Min(productsSold) ASC limit 10;

Q16. Display record of top 110 users who have used ecommerce platform recently.

SELECT gender,country,identifierhash,dayssincelastlogin from users_data order by daysSinceLastLogin ASC Limit 110;

Q17. Calculate the number of female users those who have not logged in since last 100 days.

Select count(type) as Inactive_Females from users_data where gender="F" and dayssincelastlogin >=100;

Q18. Display the number of female users of each country at ecommerce platform.

SELECT country, COUNT(Gender) as Female_Users from users_data WHERE gender="f" group by country;

Q19. Display the number of male users of each country at ecommerce platform.

SELECT country, COUNT(Gender) as Male_Users from users_data WHERE gender="M" group by country;

Q20. Calculate the average number of products sold and bought on ecommerce platform by male users for each country.

SELECT country, AVG(productsSold), AVG(productsBought) from users_data where gender="M" group by country;