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

[@vikaskumar-qr5tj]

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

[@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

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

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

[@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

[@eniolaabolade8638]

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

[@akashchristopher]

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

[@fathimafarahna2633]

🔥🔥🔥

[@firojahmed785]

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

[@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

[@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;