Shipments Booked at Each Courier Hub — SQL Joins Problem
A courier network books every shipment at an origin hub. The network planner wants a volume list of all hubs, including newly opened hubs where nothing has…
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #300
Problem statement
A courier network books every shipment at an origin hub. The network planner wants a volume list of all hubs, including newly opened hubs where nothing has been booked yet.
Return the columns hub_name and shipments (the number of shipments booked at that hub, 0 when none). Hub names are unique. Order the rows by shipments descending, then by hub_name ascending.
Tables
Table: Hub
| Column | Type |
|---|---|
| hub_id | int |
| hub_name | varchar |
| city | varchar |
Primary key: hub_id.
One row per hub.
Table: Shipment
| Column | Type |
|---|---|
| shipment_id | int |
| origin_hub_id | int |
| booked_on | date |
| weight_kg | decimal |
Primary key: shipment_id.
origin_hub_id always names a row of Hub.
Examples
Example 1
Hub
| hub_id | hub_name | city |
|---|---|---|
| 1 | Bhiwandi Mother Hub | Mumbai |
| 2 | Hoskote Gateway | Bengaluru |
| 3 | Medchal Hub | Hyderabad |
| 4 | Chakan Depot | Pune |
Shipment
| shipment_id | origin_hub_id | booked_on | weight_kg |
|---|---|---|---|
| 5001 | 1 | 2024-06-01 | 2.5 |
| 5002 | 2 | 2024-06-01 | 0.8 |
| 5003 | 1 | 2024-06-02 | 12 |
| 5004 | 3 | 2024-06-02 | 4.2 |
| 5005 | 2 | 2024-06-03 | 1.1 |
| 5006 | 1 | 2024-06-03 | 0.5 |
Output
| hub_name | shipments |
|---|---|
| Bhiwandi Mother Hub | 3 |
| Hoskote Gateway | 2 |
| Medchal Hub | 1 |
| Chakan Depot | 0 |
How to solve Shipments Booked at Each Courier Hub
We need one row per hub with a count of its shipments, and hubs with no shipments must show 0. A LEFT JOIN from Hub to Shipment keeps every hub: a hub with three bookings becomes three joined rows, a hub with none becomes one row whose shipment columns are all NULL.
Grouping by the hub then gives one row per hub, and the choice of count matters. COUNT(*) counts rows, so the empty hub would wrongly show 1; COUNT(s.shipment_id) counts non-NULL values, so it shows 0. Group by hub_id (the key) as well as the name so the grouping is right even if two hubs ever shared a name.
The order is part of the answer: most shipments first, and the name breaks ties. A correlated subquery that counts shipments per hub is an equally valid alternative — it reads each hub once and probes Shipment by origin_hub_id, which is fast with an index on that column.
Reference solution (MySQL)
SELECT h.hub_name, COUNT(s.shipment_id) AS shipments
FROM Hub h
LEFT JOIN Shipment s ON s.origin_hub_id = h.hub_id
GROUP BY h.hub_id, h.hub_name
ORDER BY shipments DESC, h.hub_nameAnother way
SELECT hub_name, (SELECT COUNT(*) FROM Shipment s WHERE s.origin_hub_id = h.hub_id) AS shipments FROM Hub h ORDER BY shipments DESC, hub_name← Drivers Without a Single Completed Trip in March 2024 · Active Fleet Vehicles by Registration State →