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

[@Szwagier-fr7ry]

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

[@theLowestPointInMyLife]

reason for that is you would never need it irl, this is just a stupid made up problem

[@SanjayKumar-sr5ft]

@@theLowestPointInMyLife yes this may be a madeup question,but this certainly improves how to approach "reccursive" question...i mean how to relate pervious iteration and current iteration,which may be a real scenario

[@vikaskumar-qr5tj]

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

[@pralaysangma8019]

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

[@ajinkyatrimukhe]

Mind blown

[@Rameshkumar-dk8me]

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

[@balukrishna2581]

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

[@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 pass.arrival_time

[@akashchristopher]

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

[@tanujreddy8597]

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

[@muhammadabbas6645]

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

[@techTFQ]

You need to test your solution with other datasets. I have shared 5 different sample data .. check if your solution works for them .. you will probably then realize why total onboarding field is required

[@Ri9t_gaming]

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

[@eniolaabolade8638]

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

[@firojahmed785]

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

[@fathimafarahna2633]

🔥🔥🔥

[@MaciejGozdur]

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

[@shubhamsom]

columns names displayed are based on what you have written at initialization state of recursion cte , since column-name was onboarded_bus at rn=1, now you might know the reason

[@tanmaykumar3250]

this one is without recursive cte using window function. Though using recursive cte looks easier and good approach. But here is my solution :
WITH bus AS (
SELECT
bus_id,
arrival_time,
IFNULL(LAG(arrival_time) OVER(ORDER BY arrival_time ASC), 0) AS prev_bus_time,
capacity
FROM buses
),
cte AS (
SELECT
b.bus_id,
b.arrival_time AS bus_arrival,
b.prev_bus_time,
b.capacity,
p.*
FROM bus AS b
LEFT JOIN Passengers AS p
ON p.arrival_time b.prev_bus_time
ORDER BY b.arrival_time
),
cte2 AS (
SELECT
bus_id,
ROUND(AVG(bus_arrival)) AS bus_arrival,
ROUND(AVG(capacity)) AS capacity,
COUNT(DISTINCT passenger_id) AS passengers
FROM cte
GROUP BY bus_id
ORDER BY ROUND(AVG(bus_arrival))
),
cte3 AS (
SELECT
*,
IF(
passengers - capacity + IF(
IFNULL(LAG(passengers) OVER(ORDER BY bus_arrival), 0) -
IFNULL(LAG(capacity) OVER(ORDER BY bus_arrival), 0) > 0,
IFNULL(LAG(passengers) OVER(ORDER BY bus_arrival), 0) -
IFNULL(LAG(capacity) OVER(ORDER BY bus_arrival), 0),
0
) > 0,
passengers - capacity + IF(
IFNULL(LAG(passengers) OVER(ORDER BY bus_arrival), 0) -
IFNULL(LAG(capacity) OVER(ORDER BY bus_arrival), 0) > 0,
IFNULL(LAG(passengers) OVER(ORDER BY bus_arrival), 0) -
IFNULL(LAG(capacity) OVER(ORDER BY bus_arrival), 0),
0
),
0
) AS passengers_on_platform
FROM cte2
)
SELECT
bus_id,
CASE
WHEN capacity >= passengers + IFNULL(LAG(passengers_on_platform) OVER(ORDER BY bus_arrival), 0)
THEN passengers + IFNULL(LAG(passengers_on_platform) OVER(ORDER BY bus_arrival), 0)
ELSE capacity
END AS passengers_in_bus
FROM cte3
ORDER BY bus_id;