runtimetask Gaps & islands · hard
Merged maintenance downtime
For completed maintenance, merge overlapping [started_at, completed_at) jobs per device. Return device_id, window_start, window_end, jobs and downtime_hours (window length in hours, 1 decimal).
what to return
- Columns
device_idwindow_startwindow_endjobsdowntime_hoursin this order- Rows
- 146 rows · any order
- Skill
- Avoid double-counting overlapping intervals. Refresher: Sessionize an event stream →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result146 rows · any order
| device_id | window_start | window_end | jobs | downtime_hours |
|---|---|---|---|---|
| 1 | 2025-04-10 13:00:00 | 2025-04-10 14:43:00 | 2 | 1.7 |
| 3 | 2025-04-24 17:00:00 | 2025-04-24 18:00:00 | 1 | 1 |
| 4 | 2025-04-23 20:00:00 | 2025-04-23 21:27:00 | 1 | 1.4 |
first 3 of 146 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.
maintenance7 columns · 168 rows
| maintenance_idnumber · unique id | device_idnumber · links to devices | maintenance_typetext | techniciantext | started_atdate & time | completed_atdate & time | costdecimal |
|---|---|---|---|---|---|---|
| 1 | 14 | calibration | Marco Clark | 2025-03-04 15:00:00 | 2025-03-04 15:57:00 | 97.02 |
| 2 | 102 | calibration | Mohammed Okafor | 2025-03-05 16:00:00 | 2025-03-05 16:29:00 | 97.13 |
| 3 | 172 | calibration | Andre Hayes | 2025-03-06 16:00:00 | 2025-03-06 17:25:00 | 67.12 |
| 4 | 110 | repair | Paul Turner | 2025-03-07 08:00:00 | 2025-03-07 10:00:00 | 425.28 |
| 5 | 146 | calibration | Mohammed Okafor | 2025-03-07 19:00:00 | 2025-03-07 19:24:00 | 49.06 |
| 6 | 31 | repair | Daniela Torres | 2025-03-07 22:00:00 | 2025-03-08 00:00:00 | 542.32 |
- maintenance_idnumber · unique id
- 1
- device_idnumber · links to devices
- 14
- maintenance_typetext
- calibration
- techniciantext
- Marco Clark
- started_atdate & time
- 2025-03-04 15:00:00
- completed_atdate & time
- 2025-03-04 15:57:00
- costdecimal
- 97.02
- maintenance_idnumber · unique id
- 2
- device_idnumber · links to devices
- 102
- maintenance_typetext
- calibration
- techniciantext
- Mohammed Okafor
- started_atdate & time
- 2025-03-05 16:00:00
- completed_atdate & time
- 2025-03-05 16:29:00
- costdecimal
- 97.13
- maintenance_idnumber · unique id
- 3
- device_idnumber · links to devices
- 172
- maintenance_typetext
- calibration
- techniciantext
- Andre Hayes
- started_atdate & time
- 2025-03-06 16:00:00
- completed_atdate & time
- 2025-03-06 17:25:00
- costdecimal
- 67.12
- maintenance_idnumber · unique id
- 4
- device_idnumber · links to devices
- 110
- maintenance_typetext
- repair
- techniciantext
- Paul Turner
- started_atdate & time
- 2025-03-07 08:00:00
- completed_atdate & time
- 2025-03-07 10:00:00
- costdecimal
- 425.28
- maintenance_idnumber · unique id
- 5
- device_idnumber · links to devices
- 146
- maintenance_typetext
- calibration
- techniciantext
- Mohammed Okafor
- started_atdate & time
- 2025-03-07 19:00:00
- completed_atdate & time
- 2025-03-07 19:24:00
- costdecimal
- 49.06
- maintenance_idnumber · unique id
- 6
- device_idnumber · links to devices
- 31
- maintenance_typetext
- repair
- techniciantext
- Daniela Torres
- started_atdate & time
- 2025-03-07 22:00:00
- completed_atdate & time
- 2025-03-08 00:00:00
- costdecimal
- 542.32
first 6 of 168 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.