Last Person on Bus
Amazon
Problem Statement
Amazon logistics needs to calculate bus boarding limits. People board a bus in an order defined by their turn. The bus has a maximum capacity of 1000kg.
Write an SQL query to find the person_name of the last person who can board the bus without exceeding the 1000kg limit.
Rules:
- Boarding order is strictly defined by the turn column.
- You must find the person with the highest turn whose cumulative weight (including everyone before them) is less than or equal to 1000.
- Return only the person_name.
Tests your understanding of
Basic SQL, Window Functions, Cumulative Sum and LIMIT
Input Tables
queue
| person_id(INTEGER) | person_name(VARCHAR) | weight(INTEGER) | turn(INTEGER) |
|---|---|---|---|
| 5 | Alice | 250 | 1 |
| 3 | Alex | 350 | 2 |
| 6 | John | 400 | 3 |
| 2 | Winston | 200 | 4 |
| 7 | Marie | 200 | 5 |
| 1 | Bob | 175 | 6 |
| 4 | Thomas | 100 | 7 |
| 8 | Sarah | 150 | 8 |
| 9 | Mike | 50 | 9 |
| 10 | Jane | 300 | 10 |
| 11 | Kevin | 100 | 11 |
| 12 | Laura | 150 | 12 |
Expected Output
| person_name(VARCHAR) |
|---|
| John |
Tags
MediumBasic SQLWindow FunctionsCumulative SumLIMIT
15-20 min
50%