Sensors Silent Through January

Easy
250.

Sensors Silent Through January

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

ColumnTypeValues
sensor_idint
machine_idint
sensor_typeenumtemperature, vibration, pressure, current

Primary key: sensor_id.

One row per installed sensor.

Table: SensorReading

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 may come back in any order; the columns must be the ones shown, in that order.

Sensor

sensor_idmachine_idsensor_type
111temperature
121vibration
132pressure
143current
153temperature

SensorReading

reading_idsensor_idread_atvalue
1112025-01-03 08:15:0071.5
2122024-12-31 23:59:592.4
3122025-02-01 00:00:002.6
4132025-01-20 14:00:00NULL
5142025-01-31 23:59:0012.8
6112025-01-09 10:00:0072.1

Output

sensor_idmachine_id
121
132
153
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.

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

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

Primary key: sensor_id.

One row per installed sensor.

Table: SensorReading

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_idmachine_idsensor_type
111temperature
121vibration
132pressure
143current
153temperature

SensorReading

reading_idsensor_idread_atvalue
1112025-01-03 08:15:0071.5
2122024-12-31 23:59:592.4
3122025-02-01 00:00:002.6
4132025-01-20 14:00:00NULL
5142025-01-31 23:59:0012.8
6112025-01-09 10:00:0072.1

Output

sensor_idmachine_id
121
132
153

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 NULL

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