Couriers Delivering More Than Their Hub's Average in June

Medium
315.

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

ColumnType
courier_idint
namevarchar
hub_idint

Primary key: courier_id.

Table: Delivery

ColumnType
delivery_idint
courier_idint
delivered_ondate

Primary key: delivery_id.

One row per parcel delivered. courier_id always names a row of Courier.

Example

The rows must come back in the order shown.

Courier

courier_idnamehub_id
1Mahesh10
2Anwar10
3Selvi10
4Pradeep20
5Joseph20

Delivery

delivery_idcourier_iddelivered_on
112024-06-03
212024-06-04
312024-06-05
422024-06-03
522024-05-31
642024-06-10
752024-06-11
832024-07-01

Output

hub_idcourier_idnamedeliveries
101Mahesh3
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.

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

ColumnType
courier_idint
namevarchar
hub_idint

Primary key: courier_id.

Table: Delivery

ColumnType
delivery_idint
courier_idint
delivered_ondate

Primary key: delivery_id.

One row per parcel delivered. courier_id always names a row of Courier.

Examples

Example 1

Courier

courier_idnamehub_id
1Mahesh10
2Anwar10
3Selvi10
4Pradeep20
5Joseph20

Delivery

delivery_idcourier_iddelivered_on
112024-06-03
212024-06-04
312024-06-05
422024-06-03
522024-05-31
642024-06-10
752024-06-11
832024-07-01

Output

hub_idcourier_idnamedeliveries
101Mahesh3

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_id

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

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