[leetcode] 1204. Last Person to Fit in the Bus / 버스 마지막 승객 구하기

1204. Last Person to Fit in the Bus

 

https://leetcode.com/problems/last-person-to-fit-in-the-bus/

Last Person to Fit in the Bus - LeetCode

 

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.

 

Example

Input: 
Queue table:
+-----------+-------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------+--------+------+
| 5         | Alice       | 250    | 1    |
| 4         | Bob         | 175    | 5    |
| 3         | Alex        | 350    | 2    |
| 6         | John Cena   | 400    | 3    |
| 1         | Winston     | 500    | 6    |
| 2         | Marie       | 200    | 4    |
+-----------+-------------+--------+------+
Output: 
+-------------+
| person_name |
+-------------+
| John Cena   |
+-------------+

 

답

# 1
SELECT * -- q1.person_name
FROM Queue q1 JOIN Queue q2 ON q1.turn >= q2.turn
GROUP BY q1.turn
HAVING SUM(q2.weight) <= 1000 -- 몸무게가 1000될때까지 더하기
ORDER BY SUM(q2.weight) DESC -- 몸무게 합(최소1000임) 내림차순 
LIMIT 1 -- 마지막 승객만 출력

# 2
SELECT person_name 
FROM
    (SELECT person_name, weight, turn, sum(weight) over(order by turn) AS cum_sum
        FROM queue) x
WHERE cum_sum <= 1000
ORDER BY turn DESC LIMIT 1;