Amazon: Delivery Routes Loaded Beyond Their Van's Capacity
Before vans leave a delivery station, the station's dispatch lead checks that no route was planned with more packages than its van can carry.
- Difficulty: Easy
- Topics: Joins
- Dialect: MySQL
- Problem: #575
Problem statement
Before vans leave a delivery station, the station's dispatch lead checks that no route was planned with more packages than its van can carry.
Return every route whose packages is strictly greater than the capacity of the van assigned to it, with the columns route_id, station_code and overflow (packages minus capacity). Routes with no van assigned yet (van_id is NULL) are not checked. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: Van
| Column | Type |
|---|---|
| van_id | int |
| van_type | enum(Standard, Cargo XL, Electric) |
| capacity | int |
Primary key: van_id.
One row per delivery van; capacity is the most packages it can carry on one route.
Table: Route
| Column | Type |
|---|---|
| route_id | int |
| route_date | date |
| station_code | varchar |
| van_id | int |
| packages | int |
Primary key: route_id.
One row per planned delivery route; van_id is NULL until a van is assigned and otherwise always names a van.
Examples
Example 1
Van
| van_id | van_type | capacity |
|---|---|---|
| 1 | Standard | 180 |
| 2 | Cargo XL | 260 |
| 3 | Electric | 150 |
Route
| route_id | route_date | station_code | van_id | packages |
|---|---|---|---|---|
| 501 | 2025-05-12 | DBL1 | 1 | 192 |
| 502 | 2025-05-12 | DBL1 | 2 | 260 |
| 503 | 2025-05-12 | DHY2 | 3 | 151 |
| 504 | 2025-05-12 | DHY2 | NULL | 300 |
| 505 | 2025-05-13 | DBL1 | 1 | 175 |
| 506 | 2025-05-13 | DHY2 | 2 | 288 |
Output
| route_id | station_code | overflow |
|---|---|---|
| 501 | DBL1 | 12 |
| 503 | DHY2 | 1 |
| 506 | DHY2 | 28 |
How to solve Amazon: Delivery Routes Loaded Beyond Their Van's Capacity
The check compares a column of Route with a column of Van, so the first step is an inner join on van_id, which puts each route next to the van it was given. The filter packages > capacity keeps the overloaded ones and packages - capacity is the overflow the dispatcher has to move to another route.
Routes still waiting for a van have van_id NULL. NULL = v.van_id is never true, so the inner join leaves them out, which is what the statement asks — there is no capacity to check them against yet. A route loaded to exactly its van's capacity is full, not over, so the comparison is strict.
The alternative looks the capacity up with a scalar subquery instead of a join; for an unassigned route the subquery returns NULL, the overflow is NULL, and overflow > 0 drops it the same way. With the van table keyed on van_id both are a single pass over the routes.
Reference solution (MySQL)
SELECT r.route_id, r.station_code, r.packages - v.capacity AS overflow
FROM Route r
JOIN Van v ON v.van_id = r.van_id
WHERE r.packages > v.capacityAnother way
SELECT route_id, station_code, overflow FROM (SELECT route_id, station_code, packages - (SELECT capacity FROM Van v WHERE v.van_id = r.van_id) AS overflow FROM Route r) t WHERE overflow > 0← Amazon: Search Queries Mentioning Each Brand · Amazon: Associates Meeting the 100 Units-per-Hour Pick Target →