Supplier Average Lead Time in Days

Medium
381.

Supplier Average Lead Time in Days

A supplier's lead time on a purchase order is the number of days from order_date to received_date. Orders not yet received (received_date NULL) have no lead time and are ignored. To avoid judging a supplier on a single delivery, only suppliers with at least 2 received orders are ranked.

Return supplier_name, received_pos (the number of received orders) and avg_lead_days (the average lead time, rounded to 2 decimal places). Order the rows by avg_lead_days lowest first, then by supplier_name.

Tables

Table: Supplier

ColumnType
supplier_idint
supplier_namevarchar

Primary key: supplier_id.

Supplier names are unique.

Table: PurchaseOrder

ColumnType
po_idint
supplier_idint
order_datedate
received_datedate

Primary key: po_id.

received_date is NULL while the goods have not arrived.

Example

The rows must come back in the order shown.

Supplier

supplier_idsupplier_name
1Sharma Traders
2Annapurna Foods
3Nandi Dairy Supplies
4Ganesh Packaging

PurchaseOrder

po_idsupplier_idorder_datereceived_date
1112025-01-022025-01-09
1212025-01-202025-01-25
1312025-02-03NULL
1422025-01-052025-01-11
1522025-01-282025-02-03
1632025-01-102025-01-11
1732025-01-142025-01-16
1832025-01-302025-01-31
1942025-01-082025-01-12

Output

supplier_namereceived_posavg_lead_days
Nandi Dairy Supplies31.33
Annapurna Foods26
Sharma Traders26
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.

Supplier Average Lead Time in Days — SQL Joins Problem

A supplier's lead time on a purchase order is the number of days from order_date to received_date.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #381

Problem statement

A supplier's lead time on a purchase order is the number of days from order_date to received_date. Orders not yet received (received_date NULL) have no lead time and are ignored. To avoid judging a supplier on a single delivery, only suppliers with at least 2 received orders are ranked.

Return supplier_name, received_pos (the number of received orders) and avg_lead_days (the average lead time, rounded to 2 decimal places). Order the rows by avg_lead_days lowest first, then by supplier_name.

Tables

Table: Supplier

ColumnType
supplier_idint
supplier_namevarchar

Primary key: supplier_id.

Supplier names are unique.

Table: PurchaseOrder

ColumnType
po_idint
supplier_idint
order_datedate
received_datedate

Primary key: po_id.

received_date is NULL while the goods have not arrived.

Examples

Example 1

Supplier

supplier_idsupplier_name
1Sharma Traders
2Annapurna Foods
3Nandi Dairy Supplies
4Ganesh Packaging

PurchaseOrder

po_idsupplier_idorder_datereceived_date
1112025-01-022025-01-09
1212025-01-202025-01-25
1312025-02-03NULL
1422025-01-052025-01-11
1522025-01-282025-02-03
1632025-01-102025-01-11
1732025-01-142025-01-16
1832025-01-302025-01-31
1942025-01-082025-01-12

Output

supplier_namereceived_posavg_lead_days
Nandi Dairy Supplies31.33
Annapurna Foods26
Sharma Traders26

How to solve Supplier Average Lead Time in Days

Each received order contributes one number, DATEDIFF(received_date, order_date); the supplier's figure is the average of those numbers. The query filters out open orders in WHERE, joins the supplier for its name, groups by supplier, and keeps only groups with at least two rows in HAVING.

The NULL handling deserves care. AVG already skips NULL lead times, so an open order would not distort the average — but COUNT(*) counts rows, not lead times, so without the WHERE filter a supplier with one received and one open order would wrongly pass the two-order rule. The first alternative shows the other fix: COUNT(received_date) counts only non-NULL values, and SUM / COUNT reproduces the average.

The average is rounded to two places because MySQL's AVG returns a four-decimal value while other engines return a float. Grouping by supplier_id as well as the name keeps two suppliers apart even if names were ever reused. The sort breaks equal averages by name. Cost: one pass over the orders and a hash join to suppliers.

Reference solution (MySQL)

SELECT s.supplier_name,
       COUNT(*) AS received_pos,
       ROUND(AVG(DATEDIFF(po.received_date, po.order_date)), 2) AS avg_lead_days
FROM PurchaseOrder po
JOIN Supplier s ON s.supplier_id = po.supplier_id
WHERE po.received_date IS NOT NULL
GROUP BY s.supplier_id, s.supplier_name
HAVING COUNT(*) >= 2
ORDER BY avg_lead_days, s.supplier_name

Another way

SELECT s.supplier_name, COUNT(po.received_date) AS received_pos, ROUND(SUM(DATEDIFF(po.received_date, po.order_date)) / COUNT(po.received_date), 2) AS avg_lead_days FROM Supplier s JOIN PurchaseOrder po ON po.supplier_id = s.supplier_id GROUP BY s.supplier_id, s.supplier_name HAVING COUNT(po.received_date) >= 2 ORDER BY avg_lead_days, s.supplier_name

Another way

SELECT s.supplier_name, t.received_pos, t.avg_lead_days FROM Supplier s JOIN (SELECT supplier_id, COUNT(*) AS received_pos, ROUND(AVG(TIMESTAMPDIFF(DAY, order_date, received_date)), 2) AS avg_lead_days FROM PurchaseOrder WHERE received_date IS NOT NULL GROUP BY supplier_id) t ON t.supplier_id = s.supplier_id WHERE t.received_pos >= 2 ORDER BY t.avg_lead_days, s.supplier_name

← Warehouse Stock Value Including Empty Sites · Purchase Order Lines Received Short →