Project 1

I have attached the techradar.sql file

I am still sending a copy of the contents of files techradar.sql here

-- Create new schema as ecommerce
-- Import .csv file users_data into MySQL
-- (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)
use ecommerce;
show tables;
select * from users_data;
-- 3. Run SQL command to see the structure of table
desc users_data;
-- 4. Run SQL command to select first 100 rows of the database
select * from users_data limit 100;
-- 5. How many distinct values exist in table for field country and language
select count(distinct country) 'Countries', count(distinct language) 'Languages' from users_data;

-- 6. Check whether male users are having maximum followers or female users into @a, @b, @c
use ecommerce;
select gender, civilityGenderId, sum(socialNbFollowers) as "Total", dense_rank() over(order by sum(socialNbFollowers) desc) FINALRANK from users_data group by gender, civilityGenderId;
-- 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
select count(*) "Total Users With Profile Pic" from users_data where hasProfilePicture='True';
select count(*) "Total Users with any App" from users_data where hasAnyApp='True';
select count(*) "Total Users with Android App" from users_data where hasAndroidApp='True';
select count(*) "Total Users with Ios App" from users_data where hasIosApp='True';
-- 8. Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers. (Hint: consider only those users having at least 1 product bought.)
select count(identifierHash) "Total Number of Buyers", country, dense_rank() over(order by count(identifierHash) desc) FINALRANKFROMHIGHEST from users_data where productsbought>=1 group by country;
-- 9. Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers. (Hint: consider only those users having at least 1 product sold.)
select count(identifierHash) "Total Number of Sellers", country, dense_rank() over(order by count(identifierHash) asc) FINALRANKFROMLOWESTTOHIGHEST from users_data where productssold>=1 group by country;

-- 10. Display name of top 10 countries having maximum products pass rate.
use ecommerce;
select country, sum(productsPassRate) "Sum of Products Pass Rate" from users_data group by country order by sum(productsPassRate) desc limit 10;

-- 11. Calculate the number of users on an ecommerce platform for different language choices.
select language, count(*) Users from users_data group by language;

-- 12. Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform. (Hint: use UNION to answer this question.)
select gender,sum(productsWished), "ProductsWished" from users_data where gender='F' UNION select gender, sum(socialProductsLiked), "ProductsLiked" from users_data where gender="F";

-- 13, Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
select gender,sum(productsSold), "Seller" from users_data where gender='M' UNION select gender, sum(productsBought), "Buyer" from users_data where gender="M";
-- Interested Seller but no buyer where productslisted but never sold and interested buywere products wished but never bought
select gender, productslisted, productssold from users_data where gender="M" and productslisted>=1 and productssold=0;
-- Interested buyer but did not purchase
select productswished, productsbought from users_data where gender="M" and productswished>=1 and productsbought=0;

-- 14. Which country is having maximum number of buyers?
select country , sum(productsBought) ProductsBought from users_data group by country order by sum(productsBought) desc limit 1;
-- 15. List the name of 10 countries having zero number of sellers.
select country, (productsSold) from users_data where productsSold=0 group by country limit 10;
-- 16. Display record of top 110 users who have used ecommerce platform recently.
select identifierHash,gender, daysSinceLastLogin from users_data order by daysSinceLastLogin asc limit 110;
-- 17.Calculate the number of female users those who have not logged in since last 100 days.
-- daysSinceLastLogin is number of days between last login to today
select gender,count(daysSinceLastLogin) "NooffemaleUsers" from users_data where gender='F' and daysSinceLastLogin > 100;

-- 18. Display the number of female users of each country at ecommerce platform. --count(*) counts each record by condition
select gender,country,count(*) NoofFemaleUsers from users_data where gender='F' group by country order by country asc;
-- 19. Display the number of male users of each country at ecommerce platform.
select gender,country,count(*) NoofMalesUsers from users_data where gender='M' group by country order by country asc;
-- 20. Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
select country,gender,avg(productsSold) "AverageProductsSold", avg(productsBought) "AverageProductsBought" from users_data where gender='M' group by country order by country asc