Last Person to Fit in the Bus
Task: Find the name of the last person that can fit on the bus without exceeding the weight limit.
Table: Queue
+-------------+-------------+
| Column Name | Type |
+-------------+-------------+
| person_id | INT |
| person_name | varchar(30) |
| weight | INT |
| turn | INT |
+-------------+-------------+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 |
+-------------+View Reference Solution & Explanation ▼
This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, a `HAVING` clause to filter the aggregated groups, and `LIMIT 1` to return only the top record after sorting.
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 q1.turn DESC LIMIT 1;