Amazon: Delivery Routes Loaded Beyond Their Van's Capacity

Easy
575.

Amazon: Delivery Routes Loaded Beyond Their Van's Capacity

EasyJoins

MySQL

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

ColumnTypeValues
van_idint
van_typeenumStandard, Cargo XL, Electric
capacityint

Primary key: van_id.

One row per delivery van; capacity is the most packages it can carry on one route.

Table: Route

ColumnType
route_idint
route_datedate
station_codevarchar
van_idint
packagesint

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Van

van_idvan_typecapacity
1Standard180
2Cargo XL260
3Electric150

Route

route_idroute_datestation_codevan_idpackages
5012025-05-12DBL11192
5022025-05-12DBL12260
5032025-05-12DHY23151
5042025-05-12DHY2NULL300
5052025-05-13DBL11175
5062025-05-13DHY22288

Output

route_idstation_codeoverflow
501DBL112
503DHY21
506DHY228
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
van_idint
van_typeenum(Standard, Cargo XL, Electric)
capacityint

Primary key: van_id.

One row per delivery van; capacity is the most packages it can carry on one route.

Table: Route

ColumnType
route_idint
route_datedate
station_codevarchar
van_idint
packagesint

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_idvan_typecapacity
1Standard180
2Cargo XL260
3Electric150

Route

route_idroute_datestation_codevan_idpackages
5012025-05-12DBL11192
5022025-05-12DBL12260
5032025-05-12DHY23151
5042025-05-12DHY2NULL300
5052025-05-13DBL11175
5062025-05-13DHY22288

Output

route_idstation_codeoverflow
501DBL112
503DHY21
506DHY228

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.capacity

Another 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 →