리트코드 1204. Last Person to Fit in the Bus

5번 정도 시도했는데 못풀었던 문제를 드디어 풀었다 ! 

이번 문제를 풀면서 배운 것 >> 누적 문제 풀때는 무조건 윈도우함수 기억하자 :) 

Table: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id is the primary key column for this table.
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 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.

 

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    | ___          |
+------+----+-----------+--------+--------------+

 

이전에는 계속 JOIN해서 풀거나 했었는데 이번에는 아예 윈도우 함수를 통해서 풀었다

완전 짧고 간단하게 풀어서 다행이다 ㅎㅎ

SELECT person_name 
FROM (
      SELECT *, SUM(Weight) OVER (ORDER BY turn RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total
      FROM Queue
     ) AS NEW
WHERE Total <= 1000
ORDER BY Total DESC
LIMIT 1

 

다른 사람들 풀이도 보았는데 대부분이 윈도우 함수를 활용해서 풀었고

몇몇이 JOIN을 하는 방식으로 풀었는데 자꾸 이런 문제 풀 때 JOIN에서 ON하는게 헷갈려서 못풀었었다

아래에 나온 것처럼 꼭 ON이 '='이 아닌 부등호로도 진행이 가능하다는 것을 기억하자 :) 

SELECT q1.person_name
FROM Queue q1 JOIN Queue q2 ON q1.turn >= q2.turn
GROUP BY q1.turn
HAVING SUM(q2.weight) <= 1000
ORDER BY SUM(q2.weight) DESC
LIMIT 1