Three consecutive high-temperature readings

Question

Cold-chain monitoring raises an incident when a freezer records a temperature above 8 degrees on **three or more consecutive readings** (ordered by `reading_time` within each `freezer_code`).

From `freezer_readings`, return the distinct `freezer_code` values that triggered at least one such inciden

Accepted Answer

Step 1: Flag each reading as high or not with a `CASE` expression, and number the readings per freezer with `ROW_NUMBER() OVER (PARTITION BY freezer_code ORDER BY reading_time)`. Step 2: Apply the **gaps-and-islands** trick: subtract a second `ROW_NUMBER()` computed per (freezer, flag) from the first. Consecutive rows sharing the same flag produce a constant difference, which becomes a streak identifier. Step 3: Keep only high-temperature rows (`is_high = 1`) and group by freezer plus `streak_key` — each group is one uninterrupted run of high readings. Step 4: `HAVING COUNT(*) >= 3` keeps runs of three or more. Step 5: Select `freezer_code`; grouping already collapses each qualifying run, and a freezer with several runs still resolves to distinct codes in the result.

Three consecutive high-temperature readings

More Window Functions questions

Asked at Oracle