Couriers Delivering More Than Their Hub's Average in June
Each courier belongs to one hub. A hub's average is its June 2024 deliveries divided by the number of couriers at the hub, counting couriers who delivered…
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #315
Problem statement
Each courier belongs to one hub. A hub's average is its June 2024 deliveries divided by the number of couriers at the hub, counting couriers who delivered nothing in June.
Return hub_id, courier_id, name and deliveries (the courier's June 2024 deliveries) for every courier whose deliveries are strictly above their hub's average. Order by hub_id, then deliveries descending, then courier_id.
Tables
Table: Courier
| Column | Type |
|---|---|
| courier_id | int |
| name | varchar |
| hub_id | int |
Primary key: courier_id.
Table: Delivery
| Column | Type |
|---|---|
| delivery_id | int |
| courier_id | int |
| delivered_on | date |
Primary key: delivery_id.
One row per parcel delivered. courier_id always names a row of Courier.
Examples
Example 1
Courier
| courier_id | name | hub_id |
|---|---|---|
| 1 | Mahesh | 10 |
| 2 | Anwar | 10 |
| 3 | Selvi | 10 |
| 4 | Pradeep | 20 |
| 5 | Joseph | 20 |
Delivery
| delivery_id | courier_id | delivered_on |
|---|---|---|
| 1 | 1 | 2024-06-03 |
| 2 | 1 | 2024-06-04 |
| 3 | 1 | 2024-06-05 |
| 4 | 2 | 2024-06-03 |
| 5 | 2 | 2024-05-31 |
| 6 | 4 | 2024-06-10 |
| 7 | 5 | 2024-06-11 |
| 8 | 3 | 2024-07-01 |
Output
| hub_id | courier_id | name | deliveries |
|---|---|---|---|
| 10 | 1 | Mahesh | 3 |
How to solve Couriers Delivering More Than Their Hub's Average in June
Build the per-courier June counts first. A LEFT JOIN from Courier to Delivery, with the June condition in the ON clause, keeps couriers who delivered nothing and counts them as 0 (COUNT(d.delivery_id) ignores the NULL row). Filtering June in WHERE instead would delete those couriers — and the hub averages would silently rise.
Then each courier is compared with their own hub's average: a correlated subquery over the same CTE, (SELECT AVG(deliveries) FROM june k WHERE k.hub_id = j.hub_id), re-evaluated for every outer row. The comparison is strict, so a courier exactly at the average is left out.
The first alternative avoids averages altogether — deliveries × couriers_at_hub > hub_total is the same test in integers. The second uses the window AVG(COUNT(...)) OVER (PARTITION BY hub_id), which averages the grouped counts per hub in one pass. All of them are cheap at this size; the window form scales best.
Reference solution (MySQL)
WITH june AS (
SELECT c.courier_id, c.name, c.hub_id, COUNT(d.delivery_id) AS deliveries
FROM Courier c
LEFT JOIN Delivery d ON d.courier_id = c.courier_id
AND d.delivered_on BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY c.courier_id, c.name, c.hub_id
)
SELECT j.hub_id, j.courier_id, j.name, j.deliveries
FROM june j
WHERE j.deliveries > (SELECT AVG(k.deliveries) FROM june k WHERE k.hub_id = j.hub_id)
ORDER BY j.hub_id, j.deliveries DESC, j.courier_idAnother way
SELECT hub_id, courier_id, name, deliveries FROM (
SELECT c.hub_id, c.courier_id, c.name,
(SELECT COUNT(*) FROM Delivery d WHERE d.courier_id = c.courier_id AND d.delivered_on >= '2024-06-01' AND d.delivered_on < '2024-07-01') AS deliveries
FROM Courier c
) x
WHERE deliveries * (SELECT COUNT(*) FROM Courier c2 WHERE c2.hub_id = x.hub_id) >
(SELECT COUNT(*) FROM Delivery d2 JOIN Courier c3 ON c3.courier_id = d2.courier_id
WHERE c3.hub_id = x.hub_id AND d2.delivered_on >= '2024-06-01' AND d2.delivered_on < '2024-07-01')
ORDER BY hub_id, deliveries DESC, courier_idAnother way
SELECT hub_id, courier_id, name, deliveries FROM (
SELECT c.hub_id, c.courier_id, c.name, COUNT(d.delivery_id) AS deliveries,
AVG(COUNT(d.delivery_id)) OVER (PARTITION BY c.hub_id) AS hub_avg
FROM Courier c LEFT JOIN Delivery d ON d.courier_id = c.courier_id AND MONTH(d.delivered_on) = 6 AND YEAR(d.delivered_on) = 2024
GROUP BY c.hub_id, c.courier_id, c.name
) t WHERE deliveries > hub_avg
ORDER BY hub_id, deliveries DESC, courier_id← Driver Idle Gaps Longer Than 90 Minutes · Each Driver's Longest Streak of Active Days →