[SQL 문제 풀이] Last Person to Fit in the Bus (버스에 마지막으로 탑승한 사람)

May 29, 2025

[SQL 문제 풀이] Last Person to Fit in the Bus (버스에 마지막으로 탑승한 사람)

Contents

Last Person to Fit in the Bus - LeetCode

Can you solve this real interview question? 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. 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 | ___ | +------+----+-----------+--------+--------------+

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

Last Person to Fit in the Bus - LeetCode


버스 탑승을 기다리는 사람들이 줄을 서 있습니다.
하지만 버스의 무게 제한이 1000킬로그램이기 때문에 탑승하지 못하는 사람들도 있을 수 있습니다.

무게 제한을 초과하지 않고 버스에 탈 수 있는 마지막 사람의 person_name을 찾기 위한 솔루션을 작성합니다.
첫 번째 사람이 무게 제한을 초과하지 않도록 테스트 케이스가 생성됩니다.

버스는 한 번에 한 사람만 탑승할 수 있습니다.

내가 작성한 쿼리

Oracle


-- 탑승 순서대로 총 무게를 더해가는 CTE
WITH weight_queue AS(
    SELECT
        person_name,
        SUM(weight) OVER (ORDER BY turn) AS Total_Weight
    FROM Queue
    ORDER BY total_weight DESC
)

SELECT person_name
FROM weight_queue
WHERE total_weight <= 1000
    AND ROWNUM = 1;

---------- CTE 없이
SELECT person_name
FROM (
		-- CTE에서 하던 작업을 서브쿼리로 이동
    SELECT
        person_name,
        sum(weight) OVER (ORDER BY turn) sum_weight
    FROM queue
    ORDER BY sum_weight DESC
    ) 
WHERE sum_weight <= 1000 AND rownum = 1

MySQL


-- 탑승 순서대로 총 무게를 더해가는 CTE
WITH weight_queue AS(
    SELECT
        person_name,
        SUM(weight) OVER (ORDER BY turn) AS Total_Weight
    FROM Queue
    ORDER BY total_weight DESC
)

SELECT person_name
FROM weight_queue
WHERE Total_Weight <= 1000
LIMIT 1;

차이점

출력 개수 제한두는 방식의 차이

See more posts

[SQL 문제 풀이] Last Person to Fit in the Bus (버스에 마지막으로 탑승한 사람)

내가 작성한 쿼리

Oracle

MySQL

차이점

More articles

[JAVA 문제 풀이] 290.괄호 회전하기

[JAVA 문제 풀이] 289. 소수 만들기

[SQL 문제 풀이] Big Countries (큰 나라들)

[JAVA 문제 풀이] 288. 할인 행사