코딩테스트 SQL 코드카타 (Last person)

문제

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 a solution 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.

Note that only one person can board the bus at any given turn.

The result format is in the following example.

 

Example 1:

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   |
+-------------+
Explanation: The folowing table is ordered by the turn for simplicity.
+------+----+-----------+--------+--------------+
| Turn | ID | Name      | Weight | Total Weight |
+------+----+-----------+--------+--------------+
| 1    | 5  | Alice     | 250    | 250          |
| 2    | 3  | Alex      | 350    | 600          |
| 3    | 6  | John Cena | 400    | 1000         | (last person to board)
| 4    | 2  | Marie     | 200    | 1200         | (cannot board)
| 5    | 4  | Bob       | 175    | ___          |
| 6    | 1  | Winston   | 500    | ___          |
+------+----+-----------+--------+--------------+

 

---

풀이

1. 해당 턴과 해당 턴 +1 의 weight 를 더해 total_weight를 업데이트 시킨다.
2. 만약 해당 턴 +1 의 total_weight 가 1000이 초과된다면, 이때 해당 턴의 사용자를 반환시킨다.
3. 즉 n+1 의 turn의 total_weight 가 >= 1000 일때 n의 Name을 반환.

 

변경

1. total_weight를 생성
2. total_weight를 역순으로 배치
3. total_weight <=1000이 만족하는 마지막 turn의 사람 이름 반환

 

---

코드

with sum as (
    select turn, 
    sum(weight) over (order by turn) as total_weight
    from Queue
)

select q.person_name
from Queue q
join sum s
on q.turn = s.turn
where s.total_weight <= 1000
order by s.total_Weight desc limit 1

 

sum 이라는 새로운 인스턴트 테이블을 생성. 

total_weight를 만들어주고,

이를 Queue 와 결합하여서 total_weight를 역순으로 배치. 

마지막 사람의 이름을 반환.

 

---

링크

LeetCode/1204-last-person-to-fit-in-the-bus at main · K-MarkLee/LeetCode

LeetCode/1204-last-person-to-fit-in-the-bus at main · K-MarkLee/LeetCode