Sustained Vibration Breach Episodes

Hard
269.

Sustained Vibration Breach Episodes

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

ColumnType
sensor_idint
read_atdatetime
mm_sdecimal

Primary key (together): sensor_id, read_at.

mm_s is never NULL and has one decimal place.

Example

The rows must come back in the order shown.

VibrationReading

sensor_idread_atmm_s
12025-07-01 10:00:006.8
12025-07-01 10:05:007.4
12025-07-01 10:10:008.2
12025-07-01 10:15:007.9
12025-07-01 10:20:007.1
12025-07-01 10:25:009
12025-07-01 10:30:009.3
22025-07-01 10:00:007.5
22025-07-01 10:05:007.6
22025-07-01 10:10:007.7

Output

sensor_idepisode_startepisode_endreadingspeak_mm_s
12025-07-01 10:05:002025-07-01 10:15:0038.2
22025-07-01 10:00:002025-07-01 10:10:0037.7
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.

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

ColumnType
sensor_idint
read_atdatetime
mm_sdecimal

Primary key: sensor_id, read_at.

mm_s is never NULL and has one decimal place.

Examples

Example 1

VibrationReading

sensor_idread_atmm_s
12025-07-01 10:00:006.8
12025-07-01 10:05:007.4
12025-07-01 10:10:008.2
12025-07-01 10:15:007.9
12025-07-01 10:20:007.1
12025-07-01 10:25:009
12025-07-01 10:30:009.3
22025-07-01 10:00:007.5
22025-07-01 10:05:007.6
22025-07-01 10:10:007.7

Output

sensor_idepisode_startepisode_endreadingspeak_mm_s
12025-07-01 10:05:002025-07-01 10:15:0038.2
22025-07-01 10:00:002025-07-01 10:10:0037.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_start

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