Readings From a Stuck Cold-Storage Sensor — SQL Medium Problem

Problem statement

A cold-storage warehouse logs its temperature every ten minutes. Real readings wobble, so when the same value appears in three or more consecutive log entries, the maintenance team suspects the sensor got stuck at that value. A NULL reading means the sensor sent nothing; it is not a value and it breaks a run.

Return every temperature that appears in at least three consecutive entries (by log_id), in a column named stuck_temp. List each such value once, even if it was stuck several times. Return the rows in any order.

Tables

Table: ColdStorageLog

Primary key: log_id.

One row per log entry; log_id runs 1, 2, 3, … in time order with no gaps. temp_c is in degrees Celsius, NULL when no reading arrived.

Examples

Example 1

ColdStorageLog

Output

How to solve Readings From a Stuck Cold-Storage Sensor

"Three consecutive entries with the same value" is a statement about a row and its neighbours in log_id order. LAG(temp_c, 1) and LAG(temp_c, 2) bring the previous two readings onto each row, and a row whose reading equals both is the third (or later) entry of a run. Every run of three or more produces at least one such row, and a shorter run produces none. DISTINCT then lists each stuck value once, even when the same value got stuck twice or the run was longer than three.

NULLs need no special case: temp_c = prev1 is unknown whenever either side is NULL, so a missing reading breaks a run and is never reported itself. The first two rows have NULL from LAG and are likewise skipped.

Because the ids have no gaps, a self join on log_id + 1 and log_id + 2 finds the same triples. A third approach is the gaps-and-islands trick: within one value's rows ordered by id, log_id - ROW_NUMBER() stays constant across a run of consecutive ids and changes when another reading interrupts it, so grouping by (temp_c, run_key) and keeping groups of three or more finds every run — and it would also report run lengths if asked. The window versions sort once; the self join does two index lookups per row.

Reference solution (MySQL)

SELECT DISTINCT temp_c AS stuck_temp
FROM (
  SELECT temp_c,
         LAG(temp_c, 1) OVER (ORDER BY log_id) AS prev1,
         LAG(temp_c, 2) OVER (ORDER BY log_id) AS prev2
  FROM ColdStorageLog
) t
WHERE temp_c = prev1 AND temp_c = prev2

Another way

SELECT DISTINCT a.temp_c AS stuck_temp
FROM ColdStorageLog a
JOIN ColdStorageLog b ON b.log_id = a.log_id + 1
JOIN ColdStorageLog c ON c.log_id = a.log_id + 2
WHERE a.temp_c = b.temp_c AND b.temp_c = c.temp_c

Another way

SELECT DISTINCT temp_c AS stuck_temp
FROM (
  SELECT temp_c, log_id - ROW_NUMBER() OVER (PARTITION BY temp_c ORDER BY log_id) AS run_key
  FROM ColdStorageLog
  WHERE temp_c IS NOT NULL
) t
GROUP BY temp_c, run_key
HAVING COUNT(*) >= 3