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
| Column | Type |
|---|---|
| supplier_id | int |
| supplier_name | varchar |
Primary key: supplier_id.
Supplier names are unique.
Table: PurchaseOrder
| Column | Type |
|---|---|
| po_id | int |
| supplier_id | int |
| order_date | date |
| received_date | date |
Primary key: po_id.
received_date is NULL while the goods have not arrived.
Examples
Example 1
Supplier
| supplier_id | supplier_name |
|---|---|
| 1 | Sharma Traders |
| 2 | Annapurna Foods |
| 3 | Nandi Dairy Supplies |
| 4 | Ganesh Packaging |
PurchaseOrder
| po_id | supplier_id | order_date | received_date |
|---|---|---|---|
| 11 | 1 | 2025-01-02 | 2025-01-09 |
| 12 | 1 | 2025-01-20 | 2025-01-25 |
| 13 | 1 | 2025-02-03 | NULL |
| 14 | 2 | 2025-01-05 | 2025-01-11 |
| 15 | 2 | 2025-01-28 | 2025-02-03 |
| 16 | 3 | 2025-01-10 | 2025-01-11 |
| 17 | 3 | 2025-01-14 | 2025-01-16 |
| 18 | 3 | 2025-01-30 | 2025-01-31 |
| 19 | 4 | 2025-01-08 | 2025-01-12 |
Output
| supplier_name | received_pos | avg_lead_days |
|---|---|---|
| Nandi Dairy Supplies | 3 | 1.33 |
| Annapurna Foods | 2 | 6 |
| Sharma Traders | 2 | 6 |
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_nameAnother 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_nameAnother 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 →