Sustained Vibration Breach Episodes — SQL Problem
Condition-monitoring sensors on pumps report vibration velocity in mm/s every few minutes. A reading above 7.1 mm/s is a breach.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #269
Problem statement
Condition-monitoring sensors on pumps report vibration velocity in mm/s every few minutes. A reading above 7.1 mm/s is a breach. A single spike is noise; the reliability team wants sustained episodes: maximal runs of consecutive readings of one sensor (in time order) that are all breaches, at least 3 readings long.
Return sensor_id, episode_start and episode_end (the first and last reading times of the run), readings (its length) and peak_mm_s (its highest value). Order by sensor_id, then episode_start.
Tables
Table: VibrationReading
| Column | Type |
|---|---|
| sensor_id | int |
| read_at | datetime |
| mm_s | decimal |
Primary key: sensor_id, read_at.
mm_s is never NULL and has one decimal place.
Examples
Example 1
VibrationReading
| sensor_id | read_at | mm_s |
|---|---|---|
| 1 | 2025-07-01 10:00:00 | 6.8 |
| 1 | 2025-07-01 10:05:00 | 7.4 |
| 1 | 2025-07-01 10:10:00 | 8.2 |
| 1 | 2025-07-01 10:15:00 | 7.9 |
| 1 | 2025-07-01 10:20:00 | 7.1 |
| 1 | 2025-07-01 10:25:00 | 9 |
| 1 | 2025-07-01 10:30:00 | 9.3 |
| 2 | 2025-07-01 10:00:00 | 7.5 |
| 2 | 2025-07-01 10:05:00 | 7.6 |
| 2 | 2025-07-01 10:10:00 | 7.7 |
Output
| sensor_id | episode_start | episode_end | readings | peak_mm_s |
|---|---|---|---|---|
| 1 | 2025-07-01 10:05:00 | 2025-07-01 10:15:00 | 3 | 8.2 |
| 2 | 2025-07-01 10:00:00 | 2025-07-01 10:10:00 | 3 | 7.7 |
How to solve Sustained Vibration Breach Episodes
An episode is an island of breach readings within a sensor's time series, so the tools are the gaps-and-islands ones. Number every reading of a sensor by time (rn_all), and number it again within its kind, breach or not (rn_kind). Along a run of breaches both numbers grow by one per reading, so rn_all - rn_kind is constant on the run and jumps whenever a calm reading intervenes. Grouping the breach rows by (sensor_id, rn_all - rn_kind) gives one group per run.
Each group's MIN(read_at) and MAX(read_at) are the episode's ends, COUNT(*) its length and MAX(mm_s) its peak; HAVING COUNT(*) >= 3 drops short spikes. The threshold is strict, so a reading of 7.1 is calm and breaks a run — the same CASE must be used in the flag and in the partition so they agree.
The alternative labels runs with a running count of calm readings: every breach after the k-th calm reading carries k, and only consecutive breaches share a label. Both are a sort per sensor and one aggregation. Note that consecutive means consecutive readings, regardless of the time gap between them.
Reference solution (MySQL)
WITH tagged AS (
SELECT sensor_id, read_at, mm_s,
CASE WHEN mm_s > 7.1 THEN 1 ELSE 0 END AS breach,
ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY read_at) AS rn_all,
ROW_NUMBER() OVER (PARTITION BY sensor_id, CASE WHEN mm_s > 7.1 THEN 1 ELSE 0 END ORDER BY read_at) AS rn_kind
FROM VibrationReading
)
SELECT sensor_id, MIN(read_at) AS episode_start, MAX(read_at) AS episode_end,
COUNT(*) AS readings, MAX(mm_s) AS peak_mm_s
FROM tagged
WHERE breach = 1
GROUP BY sensor_id, rn_all - rn_kind
HAVING COUNT(*) >= 3
ORDER BY sensor_id, episode_startAnother way
WITH g AS (
SELECT sensor_id, read_at, mm_s,
SUM(CASE WHEN mm_s <= 7.1 THEN 1 ELSE 0 END) OVER (PARTITION BY sensor_id ORDER BY read_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS calm_seen
FROM VibrationReading
)
SELECT sensor_id, MIN(read_at) AS episode_start, MAX(read_at) AS episode_end, COUNT(*) AS readings, MAX(mm_s) AS peak_mm_s
FROM g WHERE mm_s > 7.1
GROUP BY sensor_id, calm_seen HAVING COUNT(*) >= 3
ORDER BY sensor_id, episode_start← Merged Alarm Downtime per Machine · Median EV Charging Session Length by City →