runtimetask Correlated subqueries · hard
Device status at the end of each day
For devices 1–20 and every date in March 2025, return device_id, day and status: the status from the latest change on or before the end of that day (NULL before the first change). Ties on changed_at go to the highest status_event_id.
what to return
- Columns
device_iddaystatusin this order- Rows
- 620 rows · any order
- Skill
- Turn change events into a daily state table. Refresher: Query plans & indexes →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result620 rows · any order
| device_id | day | status |
|---|---|---|
| 1 | 2025-03-01 | online |
| 1 | 2025-03-02 | online |
| 1 | 2025-03-03 | online |
first 3 of 620 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.
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
device_status_events5 columns · 1,231 rows
| status_event_idnumber · unique id | device_idnumber · links to devices | changed_atdate & time | statustext | reasontext |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 00:00:00 | online | period start |
| 2 | 1 | 2025-03-07 09:00:00 | offline | power loss |
| 3 | 1 | 2025-03-07 11:00:00 | online | reconnected |
| 4 | 1 | 2025-03-13 09:00:00 | offline | power loss |
| 5 | 1 | 2025-03-13 20:00:00 | online | reconnected |
| 6 | 1 | 2025-04-09 13:00:00 | degraded | reading outside normal range |
- status_event_idnumber · unique id
- 1
- device_idnumber · links to devices
- 1
- changed_atdate & time
- 2025-03-01 00:00:00
- statustext
- online
- reasontext
- period start
- status_event_idnumber · unique id
- 2
- device_idnumber · links to devices
- 1
- changed_atdate & time
- 2025-03-07 09:00:00
- statustext
- offline
- reasontext
- power loss
- status_event_idnumber · unique id
- 3
- device_idnumber · links to devices
- 1
- changed_atdate & time
- 2025-03-07 11:00:00
- statustext
- online
- reasontext
- reconnected
- status_event_idnumber · unique id
- 4
- device_idnumber · links to devices
- 1
- changed_atdate & time
- 2025-03-13 09:00:00
- statustext
- offline
- reasontext
- power loss
- status_event_idnumber · unique id
- 5
- device_idnumber · links to devices
- 1
- changed_atdate & time
- 2025-03-13 20:00:00
- statustext
- online
- reasontext
- reconnected
- status_event_idnumber · unique id
- 6
- device_idnumber · links to devices
- 1
- changed_atdate & time
- 2025-04-09 13:00:00
- statustext
- degraded
- reasontext
- reading outside normal range
first 6 of 1,231 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.