Supplier On-Time-In-Full Rate by Order Quarter

Hard
395.

Supplier On-Time-In-Full Rate by Order Quarter

A purchase order is OTIF (on time, in full) when every one of its lines had received at least its ordered quantity by the order's promised_date — receipts dated on the promised date count, later receipts do not. A line with no on-time receipt makes the whole order miss.

For each supplier and quarter of order_date (labelled like 2024-Q4), return supplier_id, order_quarter, orders, otif_orders and otif_pct = 100 × otif_orders / orders, rounded to 2 decimal places. Order the rows by supplier_id, then by order_quarter.

Tables

Table: SupplierPo

ColumnType
po_idint
supplier_idint
order_datedate
promised_datedate

Primary key: po_id.

One row per purchase order; every order has at least one line.

Table: SupplierPoLine

ColumnType
po_idint
line_noint
qty_orderedint

Primary key (together): po_id, line_no.

The lines of each purchase order.

Table: LineReceipt

ColumnType
receipt_idint
po_idint
line_noint
received_ondate
qtyint

Primary key: receipt_id.

Partial deliveries against a line; a line can have several.

Example

The rows must come back in the order shown.

SupplierPo

po_idsupplier_idorder_datepromised_date
172024-11-042024-11-12
272024-12-102024-12-18
372025-01-062025-01-14
492024-10-212024-10-30

SupplierPoLine

po_idline_noqty_ordered
11100
1240
2160
3125
4180
4220

LineReceipt

receipt_idpo_idline_noreceived_onqty
1112024-11-1060
2112024-11-1240
3122024-11-1140
4212024-12-1750
5212024-12-2010
6312025-01-1330
7412024-10-2980

Output

supplier_idorder_quarterordersotif_ordersotif_pct
72024-Q42150
72025-Q111100
92024-Q4100
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 On-Time-In-Full Rate by Order Quarter — SQL Problem

A purchase order is OTIF (on time, in full) when every one of its lines had received at least its ordered quantity by the order's promised_date — receipts…

  • Difficulty: Hard
  • Topics: Joins, Conditional Logic, Dates
  • Dialect: MySQL
  • Problem: #395

Problem statement

A purchase order is OTIF (on time, in full) when every one of its lines had received at least its ordered quantity by the order's promised_date — receipts dated on the promised date count, later receipts do not. A line with no on-time receipt makes the whole order miss.

For each supplier and quarter of order_date (labelled like 2024-Q4), return supplier_id, order_quarter, orders, otif_orders and otif_pct = 100 × otif_orders / orders, rounded to 2 decimal places. Order the rows by supplier_id, then by order_quarter.

Tables

Table: SupplierPo

ColumnType
po_idint
supplier_idint
order_datedate
promised_datedate

Primary key: po_id.

One row per purchase order; every order has at least one line.

Table: SupplierPoLine

ColumnType
po_idint
line_noint
qty_orderedint

Primary key: po_id, line_no.

The lines of each purchase order.

Table: LineReceipt

ColumnType
receipt_idint
po_idint
line_noint
received_ondate
qtyint

Primary key: receipt_id.

Partial deliveries against a line; a line can have several.

Examples

Example 1

SupplierPo

po_idsupplier_idorder_datepromised_date
172024-11-042024-11-12
272024-12-102024-12-18
372025-01-062025-01-14
492024-10-212024-10-30

SupplierPoLine

po_idline_noqty_ordered
11100
1240
2160
3125
4180
4220

LineReceipt

receipt_idpo_idline_noreceived_onqty
1112024-11-1060
2112024-11-1240
3122024-11-1140
4212024-12-1750
5212024-12-2010
6312025-01-1330
7412024-10-2980

Output

supplier_idorder_quarterordersotif_ordersotif_pct
72024-Q42150
72025-Q111100
92024-Q4100

How to solve Supplier On-Time-In-Full Rate by Order Quarter

OTIF is an "all lines" condition, and the cleanest way to evaluate one is to aggregate upwards in levels. At the line level, LEFT JOIN the receipts and sum only those dated on or before the order's promised date — the date test sits inside a CASE within the SUM, because putting it in WHERE would delete lines whose only receipts were late and make them disappear instead of fail. A line is OK when that on-time quantity reaches the ordered quantity; a line with no receipts at all gets 0 through COALESCE and fails.

At the order level, the order is OTIF exactly when every line is OK, which is MIN(line_ok) = 1. Finally the orders are grouped by supplier and a quarter label CONCAT(YEAR(order_date), '-Q', QUARTER(order_date)), and the rate is 100 × OTIF orders / orders, rounded to two places so MySQL's four-decimal division agrees with other engines.

The alternative states the order-level rule as its negation: an order misses if there exists a line whose on-time receipts fall short. A correlated subquery sums the on-time receipts per line. It reads naturally but probes receipts per line per order; the staged version is a few hash aggregations.

Reference solution (MySQL)

WITH line_status AS (
  SELECT l.po_id,
         CASE WHEN COALESCE(SUM(CASE WHEN r.received_on <= p.promised_date THEN r.qty ELSE 0 END), 0) >= l.qty_ordered
              THEN 1 ELSE 0 END AS line_ok
  FROM SupplierPoLine l
  JOIN SupplierPo p ON p.po_id = l.po_id
  LEFT JOIN LineReceipt r ON r.po_id = l.po_id AND r.line_no = l.line_no
  GROUP BY l.po_id, l.line_no, l.qty_ordered
), po_status AS (
  SELECT po_id, MIN(line_ok) AS is_otif FROM line_status GROUP BY po_id
)
SELECT p.supplier_id,
       CONCAT(YEAR(p.order_date), '-Q', QUARTER(p.order_date)) AS order_quarter,
       COUNT(*) AS orders,
       SUM(s.is_otif) AS otif_orders,
       ROUND(100 * SUM(s.is_otif) / COUNT(*), 2) AS otif_pct
FROM SupplierPo p
JOIN po_status s ON s.po_id = p.po_id
GROUP BY p.supplier_id, CONCAT(YEAR(p.order_date), '-Q', QUARTER(p.order_date))
ORDER BY p.supplier_id, order_quarter

Another way

SELECT supplier_id, order_quarter, COUNT(*) AS orders, SUM(is_otif) AS otif_orders, ROUND(100 * SUM(is_otif) / COUNT(*), 2) AS otif_pct FROM (
  SELECT p.supplier_id, CONCAT(YEAR(p.order_date), '-Q', QUARTER(p.order_date)) AS order_quarter,
         CASE WHEN EXISTS (
           SELECT 1 FROM SupplierPoLine l
           WHERE l.po_id = p.po_id
             AND l.qty_ordered > (SELECT IFNULL(SUM(r.qty), 0) FROM LineReceipt r
                                  WHERE r.po_id = l.po_id AND r.line_no = l.line_no AND r.received_on <= p.promised_date)
         ) THEN 0 ELSE 1 END AS is_otif
  FROM SupplierPo p) t
GROUP BY supplier_id, order_quarter
ORDER BY supplier_id, order_quarter

← Median Supplier Lead Time From Goods Inward · Daily Stock Carried Forward From the Last Count →