Longest Defect-Free Batch Streak per Line

Hard
267.

Longest Defect-Free Batch Streak per Line

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

ColumnType
batch_idint
line_codevarchar
produced_ondate
defectsint

Primary key: batch_id.

defects is never NULL.

Example

The rows must come back in the order shown.

LineBatch

batch_idline_codeproduced_ondefects
1L1-ASSY2025-05-010
2L1-ASSY2025-05-010
3L2-ASSY2025-05-014
4L1-ASSY2025-05-022
5L1-ASSY2025-05-020
6L2-ASSY2025-05-021
7L1-ASSY2025-05-030
8L3-PAINT2025-05-030
9L1-ASSY2025-05-030

Output

line_codelongest_streak
L1-ASSY3
L3-PAINT1
L2-ASSY0
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.

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

ColumnType
batch_idint
line_codevarchar
produced_ondate
defectsint

Primary key: batch_id.

defects is never NULL.

Examples

Example 1

LineBatch

batch_idline_codeproduced_ondefects
1L1-ASSY2025-05-010
2L1-ASSY2025-05-010
3L2-ASSY2025-05-014
4L1-ASSY2025-05-022
5L1-ASSY2025-05-020
6L2-ASSY2025-05-021
7L1-ASSY2025-05-030
8L3-PAINT2025-05-030
9L1-ASSY2025-05-030

Output

line_codelongest_streak
L1-ASSY3
L3-PAINT1
L2-ASSY0

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_code

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