runtime
119 / 139

task LAG / LEAD · medium

When did devices go silent?

Return device_id, last_seen, next_seen and silent_minutes for every gap between consecutive readings that is longer than three times the device’s reporting_interval_minutes.

what to return

Columns
device_idlast_seennext_seensilent_minutes in this order
Rows
84 rows · any order
Skill
Find gaps longer than expected between consecutive events. Refresher: Compare rows with LAG →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result84 rows · any order
device_idlast_seennext_seensilent_minutes
12025-03-13 08:38:002025-03-13 20:38:00720
32025-04-23 14:54:002025-04-23 18:54:00240
52025-03-15 06:06:002025-03-15 20:06:00840

first 3 of 84 rows

data you can query

IoT / Sensors dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

sensor_readings10 columns · 150,425 rows
reading_idnumber · unique iddevice_idnumber · links to devicessequence_numbernumberrecorded_atdate & timereceived_atdate & timevaluedecimalbattery_pctnumbergateway_idnumbersignal_rssi_dbmnumberquality_flagtext
117164582025-03-01 00:00:002025-03-01 00:00:1218.38NULLNULLNULLok
27252302025-03-01 00:00:002025-03-01 00:00:425.226722-81ok
3169568562025-03-01 00:02:002025-03-01 00:02:5340.03NULLNULLNULLok
412509472025-03-01 00:03:002025-03-01 00:03:1736.84NULLNULLNULLok
534503572025-03-01 00:02:002025-03-01 00:03:1740.22NULLNULLNULLok
6123324172025-03-01 00:03:002025-03-01 00:03:3238.37NULLNULLNULLok
reading_idnumber · unique id
1
device_idnumber · links to devices
17
sequence_numbernumber
16458
recorded_atdate & time
2025-03-01 00:00:00
received_atdate & time
2025-03-01 00:00:12
valuedecimal
18.38
battery_pctnumber
NULL
gateway_idnumber
NULL
signal_rssi_dbmnumber
NULL
quality_flagtext
ok
reading_idnumber · unique id
2
device_idnumber · links to devices
7
sequence_numbernumber
25230
recorded_atdate & time
2025-03-01 00:00:00
received_atdate & time
2025-03-01 00:00:42
valuedecimal
5.22
battery_pctnumber
67
gateway_idnumber
22
signal_rssi_dbmnumber
-81
quality_flagtext
ok
reading_idnumber · unique id
3
device_idnumber · links to devices
169
sequence_numbernumber
56856
recorded_atdate & time
2025-03-01 00:02:00
received_atdate & time
2025-03-01 00:02:53
valuedecimal
40.03
battery_pctnumber
NULL
gateway_idnumber
NULL
signal_rssi_dbmnumber
NULL
quality_flagtext
ok
reading_idnumber · unique id
4
device_idnumber · links to devices
12
sequence_numbernumber
50947
recorded_atdate & time
2025-03-01 00:03:00
received_atdate & time
2025-03-01 00:03:17
valuedecimal
36.84
battery_pctnumber
NULL
gateway_idnumber
NULL
signal_rssi_dbmnumber
NULL
quality_flagtext
ok
reading_idnumber · unique id
5
device_idnumber · links to devices
34
sequence_numbernumber
50357
recorded_atdate & time
2025-03-01 00:02:00
received_atdate & time
2025-03-01 00:03:17
valuedecimal
40.22
battery_pctnumber
NULL
gateway_idnumber
NULL
signal_rssi_dbmnumber
NULL
quality_flagtext
ok
reading_idnumber · unique id
6
device_idnumber · links to devices
123
sequence_numbernumber
32417
recorded_atdate & time
2025-03-01 00:03:00
received_atdate & time
2025-03-01 00:03:32
valuedecimal
38.37
battery_pctnumber
NULL
gateway_idnumber
NULL
signal_rssi_dbmnumber
NULL
quality_flagtext
ok

first 6 of 150,425 rows · your queries use all of them

devices9 columns · 186 rows
device_idnumber · unique idserial_numbertextsensor_type_idnumber · links to sensor_typeslocation_idnumber · links to locationsmodeltextfirmware_versiontextreporting_interval_minutesnumberinstalled_atdate & timeretired_attext
1SN-010791911ColdSense 4002.4.2602023-09-13 00:00:00NULL
2SN-011583811ColdSense 4002.4.2602024-06-30 00:00:00NULL
3SN-012375711ThermoTag T23.0.1602024-09-11 00:00:00NULL
4SN-013167611ThermoTag T22.4.2602024-05-31 00:00:00NULL
5SN-033959531EnviroNode E12.4.01202024-09-09 00:00:00NULL
6SN-034751431HygroPro2.4.21202023-07-26 00:00:00NULL
device_idnumber · unique id
1
serial_numbertext
SN-0107919
sensor_type_idnumber · links to sensor_types
1
location_idnumber · links to locations
1
modeltext
ColdSense 400
firmware_versiontext
2.4.2
reporting_interval_minutesnumber
60
installed_atdate & time
2023-09-13 00:00:00
retired_attext
NULL
device_idnumber · unique id
2
serial_numbertext
SN-0115838
sensor_type_idnumber · links to sensor_types
1
location_idnumber · links to locations
1
modeltext
ColdSense 400
firmware_versiontext
2.4.2
reporting_interval_minutesnumber
60
installed_atdate & time
2024-06-30 00:00:00
retired_attext
NULL
device_idnumber · unique id
3
serial_numbertext
SN-0123757
sensor_type_idnumber · links to sensor_types
1
location_idnumber · links to locations
1
modeltext
ThermoTag T2
firmware_versiontext
3.0.1
reporting_interval_minutesnumber
60
installed_atdate & time
2024-09-11 00:00:00
retired_attext
NULL
device_idnumber · unique id
4
serial_numbertext
SN-0131676
sensor_type_idnumber · links to sensor_types
1
location_idnumber · links to locations
1
modeltext
ThermoTag T2
firmware_versiontext
2.4.2
reporting_interval_minutesnumber
60
installed_atdate & time
2024-05-31 00:00:00
retired_attext
NULL
device_idnumber · unique id
5
serial_numbertext
SN-0339595
sensor_type_idnumber · links to sensor_types
3
location_idnumber · links to locations
1
modeltext
EnviroNode E1
firmware_versiontext
2.4.0
reporting_interval_minutesnumber
120
installed_atdate & time
2024-09-09 00:00:00
retired_attext
NULL
device_idnumber · unique id
6
serial_numbertext
SN-0347514
sensor_type_idnumber · links to sensor_types
3
location_idnumber · links to locations
1
modeltext
HygroPro
firmware_versiontext
2.4.2
reporting_interval_minutesnumber
120
installed_atdate & time
2023-07-26 00:00:00
retired_attext
NULL

first 6 of 186 rows · your queries use all of them

solution.sql
 

Hints and the answer are always available. Using them counts the question as solved with help.

When did devices go silent? — SQL practice · Runtime