Last Person to Fit in the Bus 30-11-22

link ----------------  https://www.codingninjas.com/codestudio/problems/last-person-to-fit-in-the-bus_2122058?topList=top-100-sql-problems&leftPanelTab=0


There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.

Write an SQL query to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.

The query result format is in the following example.


------------------------------- solution 1 -----------------------------------------

select q.person_name from queue q
where (select sum(q1.weight) from queue q1 
      where q1.turn<=q.turn)<=1000
order by q.turn desc
limit 1

------------------------------------ solution 2 ---------------------------------------


SELECT 
    person_name
FROM (
    SELECT
        person_name,
        weight,
        SUM(weight) OVER(ORDER BY turn, person_id) as total_weight
    FROM Queue
    ) x
WHERE total_weight <= 1000
ORDER BY total_weight DESC
LIMIT 1