runtimetask Gaps & islands · hard
Sustained warm intervals in cold storage
For cold-storage devices (sensor_type_id 1), take one reading per device and recorded_at (lowest reading_id) with a known value. Return device_id, started_at, ended_at and readings for every run of at least 3 consecutive readings above 8 °C.
what to return
- Columns
device_idstarted_atended_atreadingsin this order- Rows
- 9 rows · any order
- Skill
- Find runs of consecutive readings that break a threshold. Refresher: Rolling averages & missing dates →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result9 rows · any order
| device_id | started_at | ended_at | readings |
|---|---|---|---|
| 1 | 2025-04-09 17:38:00 | 2025-04-10 15:38:00 | 22 |
| 3 | 2025-04-24 07:54:00 | 2025-04-24 19:54:00 | 13 |
| 7 | 2025-03-17 21:00:00 | 2025-03-18 01:00:00 | 5 |
first 3 of 9 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 id | device_idnumber · links to devices | sequence_numbernumber | recorded_atdate & time | received_atdate & time | valuedecimal | battery_pctnumber | gateway_idnumber | signal_rssi_dbmnumber | quality_flagtext |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 17 | 16458 | 2025-03-01 00:00:00 | 2025-03-01 00:00:12 | 18.38 | NULL | NULL | NULL | ok |
| 2 | 7 | 25230 | 2025-03-01 00:00:00 | 2025-03-01 00:00:42 | 5.22 | 67 | 22 | -81 | ok |
| 3 | 169 | 56856 | 2025-03-01 00:02:00 | 2025-03-01 00:02:53 | 40.03 | NULL | NULL | NULL | ok |
| 4 | 12 | 50947 | 2025-03-01 00:03:00 | 2025-03-01 00:03:17 | 36.84 | NULL | NULL | NULL | ok |
| 5 | 34 | 50357 | 2025-03-01 00:02:00 | 2025-03-01 00:03:17 | 40.22 | NULL | NULL | NULL | ok |
| 6 | 123 | 32417 | 2025-03-01 00:03:00 | 2025-03-01 00:03:32 | 38.37 | NULL | NULL | NULL | ok |
- 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 id | serial_numbertext | sensor_type_idnumber · links to sensor_types | location_idnumber · links to locations | modeltext | firmware_versiontext | reporting_interval_minutesnumber | installed_atdate & time | retired_attext |
|---|---|---|---|---|---|---|---|---|
| 1 | SN-0107919 | 1 | 1 | ColdSense 400 | 2.4.2 | 60 | 2023-09-13 00:00:00 | NULL |
| 2 | SN-0115838 | 1 | 1 | ColdSense 400 | 2.4.2 | 60 | 2024-06-30 00:00:00 | NULL |
| 3 | SN-0123757 | 1 | 1 | ThermoTag T2 | 3.0.1 | 60 | 2024-09-11 00:00:00 | NULL |
| 4 | SN-0131676 | 1 | 1 | ThermoTag T2 | 2.4.2 | 60 | 2024-05-31 00:00:00 | NULL |
| 5 | SN-0339595 | 3 | 1 | EnviroNode E1 | 2.4.0 | 120 | 2024-09-09 00:00:00 | NULL |
| 6 | SN-0347514 | 3 | 1 | HygroPro | 2.4.2 | 120 | 2023-07-26 00:00:00 | NULL |
- 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.