Shipments Booked at Each Courier Hub

Easy
300.

Shipments Booked at Each Courier Hub

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

ColumnType
hub_idint
hub_namevarchar
cityvarchar

Primary key: hub_id.

One row per hub.

Table: Shipment

ColumnType
shipment_idint
origin_hub_idint
booked_ondate
weight_kgdecimal

Primary key: shipment_id.

origin_hub_id always names a row of Hub.

Example

The rows must come back in the order shown.

Hub

hub_idhub_namecity
1Bhiwandi Mother HubMumbai
2Hoskote GatewayBengaluru
3Medchal HubHyderabad
4Chakan DepotPune

Shipment

shipment_idorigin_hub_idbooked_onweight_kg
500112024-06-012.5
500222024-06-010.8
500312024-06-0212
500432024-06-024.2
500522024-06-031.1
500612024-06-030.5

Output

hub_nameshipments
Bhiwandi Mother Hub3
Hoskote Gateway2
Medchal Hub1
Chakan Depot0
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.

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

ColumnType
hub_idint
hub_namevarchar
cityvarchar

Primary key: hub_id.

One row per hub.

Table: Shipment

ColumnType
shipment_idint
origin_hub_idint
booked_ondate
weight_kgdecimal

Primary key: shipment_id.

origin_hub_id always names a row of Hub.

Examples

Example 1

Hub

hub_idhub_namecity
1Bhiwandi Mother HubMumbai
2Hoskote GatewayBengaluru
3Medchal HubHyderabad
4Chakan DepotPune

Shipment

shipment_idorigin_hub_idbooked_onweight_kg
500112024-06-012.5
500222024-06-010.8
500312024-06-0212
500432024-06-024.2
500522024-06-031.1
500612024-06-030.5

Output

hub_nameshipments
Bhiwandi Mother Hub3
Hoskote Gateway2
Medchal Hub1
Chakan Depot0

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_name

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