Sensor Threshold Breach Rate — SQL Joins Problem
Each sensor has an alarm threshold: a reading strictly above max_threshold is a breach.
- Difficulty: Medium
- Topics: Joins, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #257
Problem statement
Each sensor has an alarm threshold: a reading strictly above max_threshold is a breach. Readings with a NULL value (dropped packets) are not readings at all and count in neither number.
For every sensor with at least one valid reading, return sensor_id, sensor_type, readings (valid readings), breaches and breach_pct — breaches as a percentage of valid readings, rounded to two decimals. Sensors with no valid reading are not listed. Order by breach_pct from highest to lowest, then by sensor_id.
Tables
Table: Sensor
| Column | Type |
|---|---|
| sensor_id | int |
| sensor_type | enum(temperature, vibration, pressure, current) |
| max_threshold | decimal |
Primary key: sensor_id.
Table: Reading
| Column | Type |
|---|---|
| reading_id | int |
| sensor_id | int |
| read_at | datetime |
| value | decimal |
Primary key: reading_id.
value is NULL for a dropped packet; sensor_id is always in Sensor.
Examples
Example 1
Sensor
| sensor_id | sensor_type | max_threshold |
|---|---|---|
| 21 | temperature | 85 |
| 22 | vibration | 4.5 |
| 23 | pressure | 6 |
| 24 | current | 30 |
Reading
| reading_id | sensor_id | read_at | value |
|---|---|---|---|
| 1 | 21 | 2025-02-01 08:00:00 | 84.2 |
| 2 | 21 | 2025-02-01 08:05:00 | 86.9 |
| 3 | 21 | 2025-02-01 08:10:00 | 85 |
| 4 | 22 | 2025-02-01 08:00:00 | 5.1 |
| 5 | 22 | 2025-02-01 08:05:00 | NULL |
| 6 | 23 | 2025-02-01 08:00:00 | 5.2 |
| 7 | 21 | 2025-02-01 08:15:00 | 88.4 |
| 8 | 24 | 2025-02-01 08:00:00 | NULL |
Output
| sensor_id | sensor_type | readings | breaches | breach_pct |
|---|---|---|---|---|
| 22 | vibration | 1 | 1 | 100 |
| 21 | temperature | 4 | 2 | 50 |
| 23 | pressure | 1 | 0 | 0 |
How to solve Sensor Threshold Breach Rate
Each reading has to be compared with its own sensor's threshold, so the reading rows are joined to Sensor. After dropping NULL values in WHERE, grouping by the sensor gives COUNT(*) valid readings, and conditional aggregation — SUM(CASE WHEN r.value > s.max_threshold THEN 1 ELSE 0 END) — counts the breaches in the same pass. The percentage is the ratio of the two times 100, rounded to two decimals as asked.
NULLs need deliberate handling. Left in, a NULL value would fall into the ELSE branch and inflate the denominator without being a breach. The inner join plus value IS NOT NULL also takes care of sensors with no valid reading: they have no rows left and are not listed, so the division never sees zero. The comparison is strict, so a reading exactly on the threshold is not a breach.
The alternative LEFT JOINs everything and lets COUNT(r.value) skip NULLs, then removes empty sensors with HAVING; COUNT(IF(cond, 1, NULL)) is another spelling of the conditional count. One scan of the readings either way.
Reference solution (MySQL)
SELECT s.sensor_id, s.sensor_type,
COUNT(*) AS readings,
SUM(CASE WHEN r.value > s.max_threshold THEN 1 ELSE 0 END) AS breaches,
ROUND(100 * SUM(CASE WHEN r.value > s.max_threshold THEN 1 ELSE 0 END) / COUNT(*), 2) AS breach_pct
FROM Sensor s
JOIN Reading r ON r.sensor_id = s.sensor_id
WHERE r.value IS NOT NULL
GROUP BY s.sensor_id, s.sensor_type
ORDER BY breach_pct DESC, s.sensor_idAnother way
SELECT s.sensor_id, s.sensor_type, COUNT(r.value) AS readings,
COUNT(IF(r.value > s.max_threshold, 1, NULL)) AS breaches,
ROUND(100 * COUNT(IF(r.value > s.max_threshold, 1, NULL)) / COUNT(r.value), 2) AS breach_pct
FROM Sensor s LEFT JOIN Reading r ON r.sensor_id = s.sensor_id
GROUP BY s.sensor_id, s.sensor_type
HAVING COUNT(r.value) > 0
ORDER BY breach_pct DESC, s.sensor_id← Machines With Over Two Hours of Unplanned Downtime · Top Operators per Shift by Good Output →