runtimetask Gaps & islands · hard
Merge overlapping incident windows
For resolved incidents, merge overlapping [opened_at, resolved_at) intervals per repository. Return repository_id, period_start, period_end and incidents for each merged period.
what to return
- Columns
repository_idperiod_startperiod_endincidentsin this order- Rows
- 270 rows · any order
- Skill
- Collapse overlapping intervals into continuous periods. Refresher: Sessionize an event stream →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result270 rows · any order
| repository_id | period_start | period_end | incidents |
|---|---|---|---|
| 1 | 2024-01-11 16:00:00 | 2024-01-11 19:53:22 | 1 |
| 5 | 2025-01-09 10:15:44 | 2025-01-09 14:11:30 | 1 |
| 7 | 2024-04-13 03:28:57 | 2024-04-13 12:20:05 | 1 |
first 3 of 270 rows
data you can query
Developer Platform 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.
incidents8 columns · 270 rows
| incident_idnumber · unique id | repository_idnumber · links to repositories | deployment_idnumber · links to deployments | severitytext | opened_atdate & time | acknowledged_atdate & time | resolved_atdate & time | commander_idnumber · links to developers |
|---|---|---|---|---|---|---|---|
| 1 | 443 | 7710 | sev3 | 2024-01-04 09:31:05 | 2024-01-04 10:08:05 | 2024-01-04 19:04:52 | 2519 |
| 2 | 427 | 7378 | sev2 | 2024-01-04 15:06:28 | 2024-01-04 15:47:28 | 2024-01-04 17:12:33 | 2537 |
| 3 | 292 | NULL | sev3 | 2024-01-08 06:00:00 | 2024-01-08 06:48:00 | 2024-01-08 07:59:36 | 1834 |
| 4 | 160 | NULL | sev3 | 2024-01-09 22:00:00 | NULL | 2024-01-10 11:10:40 | 1125 |
| 5 | 1 | NULL | sev2 | 2024-01-11 16:00:00 | 2024-01-11 17:00:00 | 2024-01-11 19:53:22 | 39 |
| 6 | 354 | NULL | sev4 | 2024-01-13 00:00:00 | 2024-01-13 00:51:00 | 2024-01-13 04:32:49 | 2112 |
- incident_idnumber · unique id
- 1
- repository_idnumber · links to repositories
- 443
- deployment_idnumber · links to deployments
- 7710
- severitytext
- sev3
- opened_atdate & time
- 2024-01-04 09:31:05
- acknowledged_atdate & time
- 2024-01-04 10:08:05
- resolved_atdate & time
- 2024-01-04 19:04:52
- commander_idnumber · links to developers
- 2519
- incident_idnumber · unique id
- 2
- repository_idnumber · links to repositories
- 427
- deployment_idnumber · links to deployments
- 7378
- severitytext
- sev2
- opened_atdate & time
- 2024-01-04 15:06:28
- acknowledged_atdate & time
- 2024-01-04 15:47:28
- resolved_atdate & time
- 2024-01-04 17:12:33
- commander_idnumber · links to developers
- 2537
- incident_idnumber · unique id
- 3
- repository_idnumber · links to repositories
- 292
- deployment_idnumber · links to deployments
- NULL
- severitytext
- sev3
- opened_atdate & time
- 2024-01-08 06:00:00
- acknowledged_atdate & time
- 2024-01-08 06:48:00
- resolved_atdate & time
- 2024-01-08 07:59:36
- commander_idnumber · links to developers
- 1834
- incident_idnumber · unique id
- 4
- repository_idnumber · links to repositories
- 160
- deployment_idnumber · links to deployments
- NULL
- severitytext
- sev3
- opened_atdate & time
- 2024-01-09 22:00:00
- acknowledged_atdate & time
- NULL
- resolved_atdate & time
- 2024-01-10 11:10:40
- commander_idnumber · links to developers
- 1125
- incident_idnumber · unique id
- 5
- repository_idnumber · links to repositories
- 1
- deployment_idnumber · links to deployments
- NULL
- severitytext
- sev2
- opened_atdate & time
- 2024-01-11 16:00:00
- acknowledged_atdate & time
- 2024-01-11 17:00:00
- resolved_atdate & time
- 2024-01-11 19:53:22
- commander_idnumber · links to developers
- 39
- incident_idnumber · unique id
- 6
- repository_idnumber · links to repositories
- 354
- deployment_idnumber · links to deployments
- NULL
- severitytext
- sev4
- opened_atdate & time
- 2024-01-13 00:00:00
- acknowledged_atdate & time
- 2024-01-13 00:51:00
- resolved_atdate & time
- 2024-01-13 04:32:49
- commander_idnumber · links to developers
- 2112
first 6 of 270 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.