[SQL 오답노트] LEET CODE 1204. Last Person to Fit in the Bus

 

문제

Table: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id column contains unique values.
This table has the information about all people waiting for a bus.
The person_id and turn columns will contain all numbers from 1 to n, where n is the number of rows in the table.
turn determines the order of which the people will board the bus, where turn=1 denotes the first person to board and turn=n denotes the last person to board.
weight is the weight of the person in kilograms.

 

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.

 

 

 

방법1 JOIN 응용으로 기본기 확실하게 

WITH total AS(
SELECT Q1.turn, SUM(Q2.weight) AS total_weight
FROM Queue Q1
JOIN Queue Q2 ON Q1.turn >= Q2.turn
GROUP BY Q1.turn)

SELECT person_name
FROM Queue Q
JOIN total T ON Q.turn = T.turn
WHERE total_weight <= 1000
ORDER BY total_weight DESC
LIMIT 1

 

설명 

- line 4

self-join을 이용해서 누적합계가 가능하도록 JOIN을 설정해 테이블을 구성한다. 

Q1.turn	Q2.turn
1	1
2	1
2	2
3	1
3	2
3	3

 

이런식으로 테이블을 구성한 뒤 GROUP BY Q1.turn을 해주고 SUM(Q2.;weight)를 해주면 누적 합계가 계산될 수 있다! ⭐⭐⭐⭐⭐

 

 

 

방법2 WINDOW 함수 사용을 통해 훨씬 짧고 간결한 쿼리로! 

WITH total AS(
SELECT *, SUM(weight) OVER(ORDER BY turn) AS total_weight
FROM Queue)

SELECT person_name
FROM total
WHERE total_weight <= 1000
ORDER BY total_weight DESC 
LIMIT 1

 

설명

- line 2

SUM(weight) OVER(ORDER BY turn)  AS total_weight: turn의 순서대로 합계를 계산해라 

누적 합계 컬럼을 쉽게 만들수있는 방법 

 

++ 윈도우 함수 OVER 정리 보기 ⭐⭐⭐