Sensors Silent Through January — SQL Joins Problem
Each machine carries IoT sensors that push readings to the plant historian.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #250
Problem statement
Each machine carries IoT sensors that push readings to the plant historian. A reading whose value is NULL is a dropped packet — the gateway saw the sensor but no measurement arrived — and does not count as the sensor reporting.
Return every sensor that sent no valid reading in January 2025 (from 2025-01-01 00:00:00 up to, but not including, 2025-02-01 00:00:00), with the columns sensor_id and machine_id. Readings outside January change nothing. Return the rows in any order.
Tables
Table: Sensor
| Column | Type |
|---|---|
| sensor_id | int |
| machine_id | int |
| sensor_type | enum(temperature, vibration, pressure, current) |
Primary key: sensor_id.
One row per installed sensor.
Table: SensorReading
| 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 | machine_id | sensor_type |
|---|---|---|
| 11 | 1 | temperature |
| 12 | 1 | vibration |
| 13 | 2 | pressure |
| 14 | 3 | current |
| 15 | 3 | temperature |
SensorReading
| reading_id | sensor_id | read_at | value |
|---|---|---|---|
| 1 | 11 | 2025-01-03 08:15:00 | 71.5 |
| 2 | 12 | 2024-12-31 23:59:59 | 2.4 |
| 3 | 12 | 2025-02-01 00:00:00 | 2.6 |
| 4 | 13 | 2025-01-20 14:00:00 | NULL |
| 5 | 14 | 2025-01-31 23:59:00 | 12.8 |
| 6 | 11 | 2025-01-09 10:00:00 | 72.1 |
Output
| sensor_id | machine_id |
|---|---|
| 12 | 1 |
| 13 | 2 |
| 15 | 3 |
How to solve Sensors Silent Through January
This is an anti join with a condition on the joined side: a sensor is silent when no reading that counts exists for it. A LEFT JOIN from Sensor keeps every sensor, and the rows with no partner come back with NULL in every column of SensorReading; WHERE r.reading_id IS NULL keeps only those.
The subtle part is where the conditions on the reading go. They must sit in the ON clause: there they decide which readings are allowed to match, so a sensor whose only reading was a dropped packet or came in February finds no partner and survives. Moved to WHERE, the same conditions would run after the join and throw away exactly the unmatched rows the question is about.
January is the half-open interval from 2025-01-01 00:00:00 to 2025-02-01 00:00:00, which needs no thought about the last second of the 31st. NOT EXISTS reads the rule most directly; NOT IN works here because sensor_id is never NULL in the subquery. With an index on (sensor_id, read_at) each form is one range lookup per sensor.
Reference solution (MySQL)
SELECT s.sensor_id, s.machine_id
FROM Sensor s
LEFT JOIN SensorReading r
ON r.sensor_id = s.sensor_id
AND r.value IS NOT NULL
AND r.read_at >= '2025-01-01 00:00:00'
AND r.read_at < '2025-02-01 00:00:00'
WHERE r.reading_id IS NULLAnother way
SELECT sensor_id, machine_id FROM Sensor s
WHERE NOT EXISTS (SELECT 1 FROM SensorReading r WHERE r.sensor_id = s.sensor_id AND r.value IS NOT NULL
AND r.read_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-31 23:59:59')Another way
SELECT sensor_id, machine_id FROM Sensor
WHERE sensor_id NOT IN (SELECT sensor_id FROM SensorReading
WHERE value IS NOT NULL AND YEAR(read_at) = 2025 AND MONTH(read_at) = 1)← Dimensional Inspection: Pass, Rework or Scrap · Production Batches Over Two Percent Defects →