SQL-Leetcode/1204-LastPersonFitInBus.sql at main · ninad-moree/SQL-Leetcode · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/*
    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. 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    | ___          |
    +------+----+-----------+--------+--------------+
*/

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