Median Supplier Lead Time From Goods Inward — SQL Problem
Averages are pulled about by one stuck delivery, so the supply-chain team quotes each supplier's median lead time instead.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #394
Problem statement
Averages are pulled about by one stuck delivery, so the supply-chain team quotes each supplier's median lead time instead. A delivery's lead time is the number of days from po_date to grn_date. With an odd number of deliveries the median is the middle value; with an even number it is the average of the two middle values.
Return supplier_id, deliveries and median_lead_days (rounded to 1 decimal place) for every supplier with at least one delivery. Order the rows by supplier_id.
Tables
Table: GoodsInward
| Column | Type |
|---|---|
| grn_id | int |
| supplier_id | int |
| po_date | date |
| grn_date | date |
Primary key: grn_id.
One row per goods-received note; grn_date is never before po_date.
Examples
Example 1
GoodsInward
| grn_id | supplier_id | po_date | grn_date |
|---|---|---|---|
| 1 | 10 | 2025-02-01 | 2025-02-04 |
| 2 | 10 | 2025-02-05 | 2025-02-12 |
| 3 | 10 | 2025-02-10 | 2025-03-02 |
| 4 | 20 | 2025-02-03 | 2025-02-08 |
| 5 | 20 | 2025-02-06 | 2025-02-08 |
| 6 | 20 | 2025-02-11 | 2025-02-20 |
| 7 | 20 | 2025-02-14 | 2025-02-17 |
| 8 | 30 | 2025-02-09 | 2025-02-13 |
Output
| supplier_id | deliveries | median_lead_days |
|---|---|---|
| 10 | 3 | 7 |
| 20 | 4 | 4 |
| 30 | 1 | 4 |
How to solve Median Supplier Lead Time From Goods Inward
MySQL has no MEDIAN() aggregate, so the median is built from its definition: sort the values, find the middle. ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY lead_days, grn_id) gives each delivery its position in sorted order (the grn_id tie-break only fixes which of equal values gets which number; the values themselves are what matter), and COUNT(*) OVER (PARTITION BY supplier_id) puts the supplier's total next to every row.
With n values the middle positions are (n + 1) DIV 2 and (n + 2) DIV 2: for n = 3 both are 2, for n = 4 they are 2 and 3. Keeping the rows at those positions and averaging them yields the middle value for an odd count and the mean of the two middle values for an even one — one formula for both cases. The average of two whole numbers ends in .0 or .5, which rounds to one decimal place exactly.
The alternative uses the counting definition: a value is a median candidate when at least half the values are ≤ it and at least half are ≥ it. For an odd count only the middle value qualifies; for an even count the two middle values do, and AVG(DISTINCT …) averages them (or returns the single value when they are equal). It is quadratic per supplier, while the window version is one sort.
Reference solution (MySQL)
WITH leads AS (
SELECT supplier_id, DATEDIFF(grn_date, po_date) AS lead_days,
ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY DATEDIFF(grn_date, po_date), grn_id) AS rn,
COUNT(*) OVER (PARTITION BY supplier_id) AS cnt
FROM GoodsInward
)
SELECT supplier_id, MAX(cnt) AS deliveries, ROUND(AVG(lead_days), 1) AS median_lead_days
FROM leads
WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2)
GROUP BY supplier_id
ORDER BY supplier_idAnother way
WITH leads AS (SELECT grn_id, supplier_id, DATEDIFF(grn_date, po_date) AS lead_days FROM GoodsInward),
counts AS (SELECT supplier_id, COUNT(*) AS cnt FROM leads GROUP BY supplier_id)
SELECT c.supplier_id, c.cnt AS deliveries,
ROUND((SELECT AVG(DISTINCT a.lead_days) FROM leads a
WHERE a.supplier_id = c.supplier_id
AND (SELECT COUNT(*) FROM leads b WHERE b.supplier_id = a.supplier_id AND b.lead_days <= a.lead_days) >= c.cnt / 2
AND (SELECT COUNT(*) FROM leads b WHERE b.supplier_id = a.supplier_id AND b.lead_days >= a.lead_days) >= c.cnt / 2), 1) AS median_lead_days
FROM counts c
ORDER BY c.supplier_id← First Day a SKU's Stock Falls Below Its Reorder Point · Supplier On-Time-In-Full Rate by Order Quarter →