Longest Defect-Free Batch Streak per Line — SQL Problem
A line's batches run one after another, in batch_id order (ids grow over time, but numbering is shared across lines, so a line's ids have gaps).
- Difficulty: Hard
- Topics: Window Functions
- Dialect: MySQL
- Problem: #267
Problem statement
A line's batches run one after another, in batch_id order (ids grow over time, but numbering is shared across lines, so a line's ids have gaps). A batch is clean when it has 0 defects. The plant celebrates long runs of consecutive clean batches on a line.
For every line that has at least one batch, return line_code and longest_streak — the largest number of consecutive batches of that line that are all clean, or 0 if none of its batches is clean. Batches of other lines in between do not break a line's streak. Order by longest_streak from highest to lowest, then by line_code.
Tables
Table: LineBatch
| Column | Type |
|---|---|
| batch_id | int |
| line_code | varchar |
| produced_on | date |
| defects | int |
Primary key: batch_id.
defects is never NULL.
Examples
Example 1
LineBatch
| batch_id | line_code | produced_on | defects |
|---|---|---|---|
| 1 | L1-ASSY | 2025-05-01 | 0 |
| 2 | L1-ASSY | 2025-05-01 | 0 |
| 3 | L2-ASSY | 2025-05-01 | 4 |
| 4 | L1-ASSY | 2025-05-02 | 2 |
| 5 | L1-ASSY | 2025-05-02 | 0 |
| 6 | L2-ASSY | 2025-05-02 | 1 |
| 7 | L1-ASSY | 2025-05-03 | 0 |
| 8 | L3-PAINT | 2025-05-03 | 0 |
| 9 | L1-ASSY | 2025-05-03 | 0 |
Output
| line_code | longest_streak |
|---|---|
| L1-ASSY | 3 |
| L3-PAINT | 1 |
| L2-ASSY | 0 |
How to solve Longest Defect-Free Batch Streak per Line
This is gaps and islands: the islands are maximal runs of clean batches within a line. Consecutive has to be measured within the line, so the order is ROW_NUMBER() OVER (PARTITION BY line_code ORDER BY batch_id) — the raw ids have gaps where other lines ran.
The classic labelling subtracts two row numbers: the position among all of the line's batches, and the position among the line's batches of the same kind (clean or dirty). Inside one clean run both increase by one per batch, so their difference is constant, and it changes whenever a dirty batch interrupts. Grouping the clean rows by (line_code, island) and counting gives each run's length; the longest per line is the MAX.
The alternative labels runs with a running total of dirty batches (SUM(…) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)): every clean batch after the k-th dirty one carries k. Counting the clean rows per label also yields 0 for a label holding only the dirty batch, so lines with no clean batch get 0 for free; the first version needs the LEFT JOIN from the distinct lines with COALESCE. Both sort each line once.
Reference solution (MySQL)
WITH flagged AS (
SELECT line_code, batch_id, defects,
ROW_NUMBER() OVER (PARTITION BY line_code ORDER BY batch_id)
- ROW_NUMBER() OVER (PARTITION BY line_code, CASE WHEN defects = 0 THEN 1 ELSE 0 END ORDER BY batch_id) AS island
FROM LineBatch
), streaks AS (
SELECT line_code, island, COUNT(*) AS len
FROM flagged
WHERE defects = 0
GROUP BY line_code, island
)
SELECT l.line_code, COALESCE(MAX(s.len), 0) AS longest_streak
FROM (SELECT DISTINCT line_code FROM LineBatch) l
LEFT JOIN streaks s ON s.line_code = l.line_code
GROUP BY l.line_code
ORDER BY longest_streak DESC, l.line_codeAnother way
WITH grouped AS (
SELECT line_code, defects,
SUM(CASE WHEN defects > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY line_code ORDER BY batch_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
FROM LineBatch
), runs AS (
SELECT line_code, grp, SUM(CASE WHEN defects = 0 THEN 1 ELSE 0 END) AS clean_run
FROM grouped GROUP BY line_code, grp
)
SELECT line_code, MAX(clean_run) AS longest_streak FROM runs GROUP BY line_code
ORDER BY longest_streak DESC, line_code← Machine OEE per Planned Shift · Merged Alarm Downtime per Machine →