All Courses
All Courses
Courses by Software
Courses by Semester
Courses by Domain
Tool-focused Courses
Machine learning
POPULAR COURSES
Success Stories
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…
Anupama Yeragudipati
updated on 23 Jan 2023
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
Leave a comment
Thanks for choosing to leave a comment. Please keep in mind that all the comments are moderated as per our comment policy, and your email will not be published for privacy reasons. Please leave a personal & meaningful conversation.
Other comments...
Project 2
First KMeans (Initial Run) Objective: To apply KMeans clustering on the car dataset (likely based on features like fuel type, city mpg, highway mpg, etc.), without much prior tuning or pre-processing. Steps: Standard KMeans clustering applied using default parameters (e.g., random initialization, fixed number of clusters).…
28 Apr 2025 12:48 PM IST
Project 1
???? Automobile Dataset (1985) Analysis – Project Report ???? Objective The purpose of this project is to perform data cleaning, exploratory data analysis (EDA), and basic machine learning modeling on the 1985 Automobile dataset. This analysis aims to uncover insights into vehicle fuel efficiency, engine performance, and trends…
09 Apr 2025 06:25 PM IST
Unsupervised Learning - Kmeans Week 11 Challenge
How does similarity is calculated if data is categorical in nature1. Hamming Distance Used when categorical variables are binary (0/1, Yes/No, True/False). It calculates the number of positions at which two strings of equal length are different. Formula: d(x,y)=∑i=1nI(xi≠yi)d(x, y) = \sum_{i=1}^{n} I(x_i \neq y_i)d(x,y)=i=1∑nI(xi=yi)…
06 Apr 2025 05:16 PM IST
Supervised Learning - Classification Week 9 Challenge
1. What is a Neural Network?A Neural Network is a computational model inspired by the human brain. It consists of layers of artificial neurons that process input data to make predictions or classifications. It is commonly used in machine learning for tasks like image recognition, speech processing, and pattern detection.…
24 Mar 2025 04:10 PM IST
Related Courses
0 Hours of Content
Skill-Lync offers industry relevant advanced engineering courses for engineering students by partnering with industry experts.
© 2025 Skill-Lync Inc. All Rights Reserved.