LEETCODE - Passenger Bus Problem - SQL Interview Query 23| SQL Problem Level 'HARD'

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the 23rd video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.

This problem is taken from LeetCode. Problem no 2153:

Learn and Practice SQL on LearnSQL platform below:

Let's follow the below routine to make the best use of it:
1. Watch the YouTube video (first half) to understand the problem statement.

2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.

3. Share your solution on Discord and discuss different solutions and issues on my Discord server.

4. Watch the second half of my YouTube video to find my solution to the problem.

5. Share it with your contacts and spread the knowledge.

DOWNLOAD the Dataset from below:

Timeline:
00:00 Intro
00:10 Understanding Problem Statement
04:58 Solution to the SQL Problem

Thanks for participating in this challenge!

Good luck and Happy Learning!

[Szwagier-frry]

:O Brain blow, thanks to this video I saw for the first time in my life Recursive function in SQL. Love it - thanks!

[vikaskumar-qrtj]

Nice way of approaching the problem and one very good use case of recursive cte..

[pralaysangma]

I learnt many things from your videos on SQL Server. Thank you Taufiq.

[Tusharchitrakar]

Thanks for finally uploading this leetcode problem based on our discussion a week back. I have also posted my MySQL solution for others. I have one request for a future video: since you focussed so much on improving the speed of the query, can you make a future video on query optimization techniques and tips? It'll be very very helpful especially from a work point of view where we would be expected to write 200-1000 lines of SQL code. Thanks!

-- to join passengers to all possible buses and calculate total passengers possible for each bus
-- View is created since a recursive cte cannot access a regular cte in mysql
create view max_passengers as

(
select bus.bus_id, bus.capacity, count(*) as max_poss
from buses as bus join passengers as pass
on group by bus.bus_id, bus.capacity order by bus.bus_id);

with recursive cte as
(select bus_id, capacity, max_poss,

-- this expression finds the minimum of capacity and max_poss: if max_poss is greater than capacity, the passengers will
-- be pushed to the next bus. Else, all passengers will be in the current bus
(case when capacity<max_poss then capacity else max_poss end) as final_pass,

-- this expression calculates the total passengers boarded so far
(case when capacity<max_poss then capacity else max_poss end)+0 as boarded_pass
from max_passengers where bus_id=1

UNION ALL

select max.bus_id, max.capacity, max.max_poss,

(case when
then max.capacity else max.max_poss-cte.boarded_pass end) as final_pass,

(case when
then max.capacity else max.max_poss-cte.boarded_pass end)+cte.boarded_pass as boarded_pass
from max_passengers as max join cte on max.bus_id=cte.bus_id+1)

-- shared all the columns for reference but final_pass is the required output

select * from cte;

[Rameshkumar-dkme]

Thanks Thoufig with nice problem, please explain the subtraction logic in detail[logic part] .

[akashchristopher]

should i expect these kind of problems from top tech companie's interviews ?

[balukrishna]

Please explain a queries related to mutual funds and stocks in this 30 days challenge.

[Rit_gaming]

Is there a way to check which passengers onboarded which bus? or can there be another case where single bus arrives multiple times.

[muhammadabbas]

Hello Sir hope you are doing well sorry to disturb you again as after watching this video more than 2 times I an unable to understand why we need total_onboarded_pessangers can you please elaborate as my answer is quite similar with yours but I didnt add total_onboarded_pessangers and got my answer right thanks you for all your videos as it makes me good in sql language God bless you and your family

[eniolaabolade]

Hi, can you please post more EDA projects using Python. I love your content 🥺

[firojahmed]

Hello,
I tried to run your MSSQL query, but it shows no rows.
Is there something I am missing?

[cvizvno]

18.49 how did you get the column total_onboarded_bus? you named two columns onboarded_bus yet they are displayed with different names