Median Supplier Lead Time From Goods Inward

Hard
394.

Median Supplier Lead Time From Goods Inward

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

ColumnType
grn_idint
supplier_idint
po_datedate
grn_datedate

Primary key: grn_id.

One row per goods-received note; grn_date is never before po_date.

Example

The rows must come back in the order shown.

GoodsInward

grn_idsupplier_idpo_dategrn_date
1102025-02-012025-02-04
2102025-02-052025-02-12
3102025-02-102025-03-02
4202025-02-032025-02-08
5202025-02-062025-02-08
6202025-02-112025-02-20
7202025-02-142025-02-17
8302025-02-092025-02-13

Output

supplier_iddeliveriesmedian_lead_days
1037
2044
3014
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.

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

ColumnType
grn_idint
supplier_idint
po_datedate
grn_datedate

Primary key: grn_id.

One row per goods-received note; grn_date is never before po_date.

Examples

Example 1

GoodsInward

grn_idsupplier_idpo_dategrn_date
1102025-02-012025-02-04
2102025-02-052025-02-12
3102025-02-102025-03-02
4202025-02-032025-02-08
5202025-02-062025-02-08
6202025-02-112025-02-20
7202025-02-142025-02-17
8302025-02-092025-02-13

Output

supplier_iddeliveriesmedian_lead_days
1037
2044
3014

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_id

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