This video is the second part of our series on SQL interview questions and answers. This series is specifically designed for people targeting jobs as data analysts, data scientists, data engineers, and business analysts.
In this video, I have explained how to use the RANK() OVER PARTITION() function to find the second highestselling product in each category.
Use the below the commands to create your own database and table:
CREATE DATABASE THEMLMINE;
USE THEMLMINE;
CREATE TABLE sales (
order_id INT PRIMARY KEY,
product_name varchar(20) NOT NULL,
units_sold INT DEFAULT 0,
unit_price FLOAT
);
CREATE TABLE products (
category varchar(20) NOT NULL,
product varchar(20)
);
INSERT INTO sales VALUES
(122,'Bikaji_namkeen',1500,200),
(112,'Lays',10000,20),
(110,'Amul_kool',2200,25),
(138,'Dairy_milk',2000,149),
(202,'Monaco',9000,50),
(118,'Coke',7000,95),
(104,'Appy_fizz',8000,35),
(189,'KitKat',4500,70),
(238,'Dosa_batter',3000,99),
(199,'Munch',4500,80),
(448,'Maggi',10000,168);
INSERT INTO products VALUES
('Snacks','Bikaji_namkeen'),
('Snacks','Lays'),
('Snacks','Monaco'),
('Drinks','Amul_kool'),
('Drinks','Coke'),
('Drinks','Appy_fizz'),
('Chocolates','KitKat'),
('Chocolates','Munch'),
('Chocolates','Dairy_milk'),
('Instant_food','Dosa_batter'),
('Instant_food','Maggi');
You can download the final script from here: https://drive.google.com/file/d/1diLH...
Learn SQL from scratch: • SQL Tutorial for beginners | Part 1...
Instagram: / the_ml_mine
Timestamps:
00:00 Introduction
00:05 Interview question
03:20 MySQL workbench
04:53 Writing SQL query
09:48 Using subquery method
11:50 Using CTE method
12:37 Outro
Music credits:
Intro music Education upbeat positive (short ver.) by AudioCoffee https://freesound.org/s/724628/
License: Attribution NonCommercial 4.0