Solving a Complex SQL Interview problem | Practice SQL Queries

In this video, let us solve a complex SQL interview problem. This is a REAL SQL Interview question that might seem impossible to solve just by using SQL at first. But during the video, I will simplify the problem by breaking it into multiple parts.
We shall first determine an approach that can solve such problems easily and then write the SQL query to solve this problem.

You can download the dataset, scripts, and solution from my website below:

🔴 My Recommended courses 👇

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

THANK YOU,
Thoufiq

[ftidcgn]

Hi, I could get the same result without using joins into the CTEs. Also, when calculating the Row Number, you can add another CTE to avoid the subquery.
The way you explain and breakdown the problem in small parts is awesome.
Thank you so much for your contribution, such a nice content great job. :)

[Siddharth_Matada]

Your logic and problem breakdown approach is awesome....anyone with zero sql knowledge can understand...Thanks for the content...do solve more and more interview questions and upload videos frequently as much as possible....

[deansonnier]

What a fantastic explanation. Great job and thank you!

[TheVishesh]

what a beautiful solution to such a complex problem statement. I am mesmerized by your critical thinking and your approach. WOW! just WOW

[bilalshahbaz]

Great 👍 instructor of SQL every concept of SQL beginners to advance awesome here

[kushalSaini]

thank you for providing such informative content. Your explanation of the concept of recursive has enabled me to comprehend it more fully. I greatly appreciate your efforts in sharing your knowledge !!

[avi]

Beautiful solution sir!!
Explanation was too good
And steps to approach the problem 💯

[puchhas]

phenomenal solving approach ! Very helpful ! Thanks much

[charanmerla]

I like your problem Breakdown, Great content🙌

[neelamkushwaha]

your problem breakdown approah is really awesome...

[nivethadinakar]

Thanks for sharing, able to understand step by step clearly

[arnabdas]

Hi,
can you please create video on query optimization and performance tuning. Also explain best case practices to write queries.
In any sql interview asking tricky questions on performance tuning is really very common. So it will help us a lot.

Thanks in advance.

[avinashpratapsingh]

Thanks for such an interesting and wonderful query question

[ryuhayabusa]

I have learned so much from you.
If you have time, could you make a video on triggers?
Thank you for your teaching.

[shashikantkrishna]

Thank you!! All videos of yours are just awesome. Can you please make a video how EXPLAIN keyword in SQL.

[faridhasani]

It was very interesting. I wanted to implement it in SQL Server

[sarikamhetre]

Hello Sir, your Sql content helps me a lot to understand each n everything about sql in depth. Apart from this it's a request to you that guide me to gain knowledge of plsql in detail..

[kanasuanH]

Love your complex SQL contents.
The first part that breakdown batch_id and order_id I actually come up with a solution using generate_series and cross join lateral.
---select batch_id, 1 as quantity, row_number () OVER (order by batch_id) as rn from batch CROSS JOIN LATERAL generate_series(1, quantity, 1);
Hope this is helpful to anyone :)

[hasibbrdar]

I think I have a better solution. Code IS written for Mysql.

-- creating shema
drop schema if exists test_schema;
create schema test_schema;
use test_schema;
SET SQL_SAFE_UPDATES = 0;

-- creating raw tables
create table batch
(BATCH_ID  char(5), QUANTITY  int);
create table orders
(ORDER_NUMBER  char(5), QUANTITY  int);

-- adding values in that tables
insert into batch
vaLues("B1", 5),
("B2", 12),
("B3", 8);
insert into orders
vaLues("O1", 2),
("O2", 8),
("O3", 2),
("O4", 5),
("O5", 9),
("O6", 5);

-- adding column 'up_border' in tables orders and batch
alter table batch
add column up_border int;
alter table orders
add column up_border int;

-- adding comulative sum of quantity in column 'up_border'
WITH v_batch_tmp AS
(    SELECT BATCH_ID,  sum(QUANTITY) over (order by BATCH_ID) as up_border
FROM batch )
update batch, v_batch_tmp
set batch.up_border = v_batch_tmp.up_border
where batch.BATCH_ID = v_batch_tmp.BATCH_ID;

WITH v_orders_tmp AS
(    SELECT ORDER_NUMBER,  sum(QUANTITY) over (order by ORDER_NUMBER) as up_border
FROM orders )
update orders, v_orders_tmp
set orders.up_border = v_orders_tmp.up_border
where orders.ORDER_NUMBER = v_orders_tmp.ORDER_NUMBER;

-- union of batches and orders for finding all up boundries
create table tmp_uni_ob
select  row_number() over ( order by up_border) as RN,
 ifnull(up_border - LAG(up_border)
OVER (ORDER BY up_border ), up_border) AS numbers,
tn.* from
(SELECT BATCH_ID as ID,  up_border
FROM batch
UNION
SELECT ORDER_NUMBER as ID, up_border
FROM orders) as tn;

-- adding right order and branch id in results
create table expected_output
SELECT  O.ORDER_NUMBER, B.BATCH_ID,
sum(t1.numbers) as numbers
FROM test_schema.tmp_uni_ob t1
inner join test_schema.orders o
on t1.up_border <= o.up_border
and t1.up_border > o.up_border-o.QUANTITY
inner join test_schema.batch B
on t1.up_border <= B.up_border
and t1.up_border > B.up_border-B.QUANTITY
group by  O.ORDER_NUMBER, B.BATCH_ID
order by  O.ORDER_NUMBER, B.BATCH_ID;

select * from expected_output;

-- droping tmp tables
drop table if exists tmp_uni_ob;

[fenix]

This was a good challenge, I followed the same approach in MySQL with slight differences:

with cte_order as (with recursive cte1 as (select order_number, 1 n from orders
union
select o.order_number, n+1 from cte1, orders o where n<o.quantity)
select order_number, row_number() over() num from cte1 order by order_number),

cte_batch as (with recursive cte2 as (select batch_id, 1 n from batch
union
select b.batch_id, n+1 from cte2, batch b where n<b.quantity)
select batch_id, row_number() over() num from cte2 order by batch_id)

select order_number, batch_id, case when count(cb.num)=0 then null else count(cb.num) end quantity
from cte_order co left join cte_batch cb on co.num=cb.num
group by order_number, batch_id;