Sensor Threshold Breach Rate

Medium
257.

Sensor Threshold Breach Rate

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

ColumnTypeValues
sensor_idint
sensor_typeenumtemperature, vibration, pressure, current
max_thresholddecimal

Primary key: sensor_id.

Table: Reading

ColumnType
reading_idint
sensor_idint
read_atdatetime
valuedecimal

Primary key: reading_id.

value is NULL for a dropped packet; sensor_id is always in Sensor.

Example

The rows must come back in the order shown.

Sensor

sensor_idsensor_typemax_threshold
21temperature85
22vibration4.5
23pressure6
24current30

Reading

reading_idsensor_idread_atvalue
1212025-02-01 08:00:0084.2
2212025-02-01 08:05:0086.9
3212025-02-01 08:10:0085
4222025-02-01 08:00:005.1
5222025-02-01 08:05:00NULL
6232025-02-01 08:00:005.2
7212025-02-01 08:15:0088.4
8242025-02-01 08:00:00NULL

Output

sensor_idsensor_typereadingsbreachesbreach_pct
22vibration11100
21temperature4250
23pressure100
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.

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

ColumnType
sensor_idint
sensor_typeenum(temperature, vibration, pressure, current)
max_thresholddecimal

Primary key: sensor_id.

Table: Reading

ColumnType
reading_idint
sensor_idint
read_atdatetime
valuedecimal

Primary key: reading_id.

value is NULL for a dropped packet; sensor_id is always in Sensor.

Examples

Example 1

Sensor

sensor_idsensor_typemax_threshold
21temperature85
22vibration4.5
23pressure6
24current30

Reading

reading_idsensor_idread_atvalue
1212025-02-01 08:00:0084.2
2212025-02-01 08:05:0086.9
3212025-02-01 08:10:0085
4222025-02-01 08:00:005.1
5222025-02-01 08:05:00NULL
6232025-02-01 08:00:005.2
7212025-02-01 08:15:0088.4
8242025-02-01 08:00:00NULL

Output

sensor_idsensor_typereadingsbreachesbreach_pct
22vibration11100
21temperature4250
23pressure100

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_id

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