Menu

Executive Programs

Workshops

Projects

Blogs

Careers

Placements

Student Reviews


For Business


More

Academic Training

Informative Articles

Find Jobs

We are Hiring!


All Courses

Choose a category

Mechanical

Electrical

Civil

Computer Science

Electronics

Offline Program

All Courses

All Courses

logo

CHOOSE A CATEGORY

Mechanical

Electrical

Civil

Computer Science

Electronics

Offline Program

Top Job Leading Courses

Automotive

CFD

FEA

Design

MBD

Med Tech

Courses by Software

Design

Solver

Automation

Vehicle Dynamics

CFD Solver

Preprocessor

Courses by Semester

First Year

Second Year

Third Year

Fourth Year

Courses by Domain

Automotive

CFD

Design

FEA

Tool-focused Courses

Design

Solver

Automation

Preprocessor

CFD Solver

Vehicle Dynamics

Machine learning

Machine Learning and AI

POPULAR COURSES

coursePost Graduate Program in Hybrid Electric Vehicle Design and Analysis
coursePost Graduate Program in Computational Fluid Dynamics
coursePost Graduate Program in CAD
coursePost Graduate Program in CAE
coursePost Graduate Program in Manufacturing Design
coursePost Graduate Program in Computational Design and Pre-processing
coursePost Graduate Program in Complete Passenger Car Design & Product Development
Executive Programs
Workshops
For Business

Success Stories

Placements

Student Reviews

More

Projects

Blogs

Academic Training

Find Jobs

Informative Articles

We're Hiring!

phone+91 9342691281Log in
  1. Home/
  2. Anupama Yeragudipati/
  3. Project 1

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…

    • 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.

    Please  login to add a comment

    Other comments...

    No comments yet!
    Be the first to add a comment

    Read more Projects by Anupama Yeragudipati (21)

    Project 2

    Objective:

    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).…

    calendar

    28 Apr 2025 12:48 PM IST

      Read more

      Project 1

      Objective:

      ???? 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…

      calendar

      09 Apr 2025 06:25 PM IST

        Read more

        Unsupervised Learning - Kmeans Week 11 Challenge

        Objective:

        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∑n​I(xi​=yi​)…

        calendar

        06 Apr 2025 05:16 PM IST

          Read more

          Supervised Learning - Classification Week 9 Challenge

          Objective:

          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.…

          calendar

          24 Mar 2025 04:10 PM IST

            Read more

            Schedule a counselling session

            Please enter your name
            Please enter a valid email
            Please enter a valid number

            Related Courses

            coursecard

            Design loads considered on bridges

            Recently launched

            10 Hours of Content

            coursecard

            Design of Steel Superstructure in Bridges

            Recently launched

            16 Hours of Content

            coursecard

            Design for Manufacturability (DFM)

            Recently launched

            11 Hours of Content

            coursecard

            CATIA for Medical Product Design

            Recently launched

            5 Hours of Content

            coursecardcoursetype

            Accelerated Career Program in Embedded Systems (On-Campus) Courseware Partner: IT-ITes SSC nasscom

            Recently launched

            0 Hours of Content

            Schedule a counselling session

            Please enter your name
            Please enter a valid email
            Please enter a valid number

            logo

            Skill-Lync offers industry relevant advanced engineering courses for engineering students by partnering with industry experts.

            https://d27yxarlh48w6q.cloudfront.net/web/v1/images/facebook.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/insta.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/twitter.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/youtube.svghttps://d27yxarlh48w6q.cloudfront.net/web/v1/images/linkedin.svg

            Our Company

            News & EventsBlogCareersGrievance RedressalSkill-Lync ReviewsTermsPrivacy PolicyBecome an Affiliate
            map
            EpowerX Learning Technologies Pvt Ltd.
            4th Floor, BLOCK-B, Velachery - Tambaram Main Rd, Ram Nagar South, Madipakkam, Chennai, Tamil Nadu 600042.
            mail
            info@skill-lync.com
            mail
            ITgrievance@skill-lync.com

            Top Individual Courses

            Computational Combustion Using Python and CanteraIntroduction to Physical Modeling using SimscapeIntroduction to Structural Analysis using ANSYS WorkbenchIntroduction to Structural Analysis using ANSYS Workbench

            Top PG Programs

            Post Graduate Program in Hybrid Electric Vehicle Design and AnalysisPost Graduate Program in Computational Fluid DynamicsPost Graduate Program in CADPost Graduate Program in Electric Vehicle Design & Development

            Skill-Lync Plus

            Executive Program in Electric Vehicle Embedded SoftwareExecutive Program in Electric Vehicle DesignExecutive Program in Cybersecurity

            Trending Blogs

            Heat Transfer Principles in Energy-Efficient Refrigerators and Air Conditioners Advanced Modeling and Result Visualization in Simscape Exploring Simulink and Library Browser in Simscape Advanced Simulink Tools and Libraries in SimscapeExploring Simulink Basics in Simscape

            © 2025 Skill-Lync Inc. All Rights Reserved.

                        Do You Want To Showcase Your Technical Skills?
                        Sign-Up for our projects.