runtimetask GROUP BY · medium
Deployments versus incidents
For every organization, return organization_id, production_deploys (production deployments of its repositories), failed_deploys (those with status failed or rolled_back) and incidents (incidents on its repositories), using 0 when none.
what to return
- Columns
organization_idproduction_deploysfailed_deploysincidentsin this order- Rows
- 60 rows · any order
- Skill
- Compare two measures from different tables per group. Refresher: Multi-grain joins without fan-out →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result60 rows · any order
| organization_id | production_deploys | failed_deploys | incidents |
|---|---|---|---|
| 1 | 82 | 10 | 6 |
| 2 | 196 | 23 | 19 |
| 3 | 58 | 5 | 3 |
first 3 of 60 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.
organizations5 columns · 60 rows
| organization_idnumber · unique id | org_nametext | plantext | countrytext | created_atdate & time |
|---|---|---|---|---|
| 1 | Fern Motors | team | United Kingdom | 2022-05-31 00:00:00 |
| 2 | Lumen Robotics | enterprise | Spain | 2021-03-04 00:00:00 |
| 3 | River Labs | enterprise | United States | 2023-04-08 00:00:00 |
| 4 | Sable Studio | enterprise | Germany | 2020-10-21 00:00:00 |
| 5 | Union Health | free | United States | 2021-03-01 00:00:00 |
| 6 | Swift Studio | team | United States | 2022-03-25 00:00:00 |
- organization_idnumber · unique id
- 1
- org_nametext
- Fern Motors
- plantext
- team
- countrytext
- United Kingdom
- created_atdate & time
- 2022-05-31 00:00:00
- organization_idnumber · unique id
- 2
- org_nametext
- Lumen Robotics
- plantext
- enterprise
- countrytext
- Spain
- created_atdate & time
- 2021-03-04 00:00:00
- organization_idnumber · unique id
- 3
- org_nametext
- River Labs
- plantext
- enterprise
- countrytext
- United States
- created_atdate & time
- 2023-04-08 00:00:00
- organization_idnumber · unique id
- 4
- org_nametext
- Sable Studio
- plantext
- enterprise
- countrytext
- Germany
- created_atdate & time
- 2020-10-21 00:00:00
- organization_idnumber · unique id
- 5
- org_nametext
- Union Health
- plantext
- free
- countrytext
- United States
- created_atdate & time
- 2021-03-01 00:00:00
- organization_idnumber · unique id
- 6
- org_nametext
- Swift Studio
- plantext
- team
- countrytext
- United States
- created_atdate & time
- 2022-03-25 00:00:00
first 6 of 60 rows · your queries use all of them
repositories9 columns · 512 rows
| repository_idnumber · unique id | organization_idnumber · links to organizations | owning_team_idnumber · links to teams | repo_nametext | primary_languagetext | visibilitytext | created_atdate & time | archived_attext | forked_from_repository_idnumber · links to repositories |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | notifier-catalog | Python | private | 2022-06-01 00:00:00 | NULL | NULL |
| 2 | 1 | 2 | billing-metrics | C# | internal | 2022-06-08 00:00:00 | NULL | NULL |
| 3 | 1 | 4 | catalog-cli | Go | private | 2022-07-23 00:00:00 | NULL | NULL |
| 4 | 1 | 4 | catalog-cli-fork | Python | internal | 2022-07-21 00:00:00 | NULL | 3 |
| 5 | 1 | 5 | cli-metrics | Go | private | 2023-07-18 00:00:00 | NULL | NULL |
| 6 | 1 | 5 | web-infra | Swift | internal | 2023-01-21 00:00:00 | NULL | NULL |
- repository_idnumber · unique id
- 1
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 1
- repo_nametext
- notifier-catalog
- primary_languagetext
- Python
- visibilitytext
- private
- created_atdate & time
- 2022-06-01 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 2
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 2
- repo_nametext
- billing-metrics
- primary_languagetext
- C#
- visibilitytext
- internal
- created_atdate & time
- 2022-06-08 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 3
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 4
- repo_nametext
- catalog-cli
- primary_languagetext
- Go
- visibilitytext
- private
- created_atdate & time
- 2022-07-23 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 4
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 4
- repo_nametext
- catalog-cli-fork
- primary_languagetext
- Python
- visibilitytext
- internal
- created_atdate & time
- 2022-07-21 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- 3
- repository_idnumber · unique id
- 5
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 5
- repo_nametext
- cli-metrics
- primary_languagetext
- Go
- visibilitytext
- private
- created_atdate & time
- 2023-07-18 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
- repository_idnumber · unique id
- 6
- organization_idnumber · links to organizations
- 1
- owning_team_idnumber · links to teams
- 5
- repo_nametext
- web-infra
- primary_languagetext
- Swift
- visibilitytext
- internal
- created_atdate & time
- 2023-01-21 00:00:00
- archived_attext
- NULL
- forked_from_repository_idnumber · links to repositories
- NULL
first 6 of 512 rows · your queries use all of them
deployments8 columns · 8,991 rows
| deployment_idnumber · unique id | repository_idnumber · links to repositories | environmenttext | commit_shatext · links to commits | triggered_by_idnumber · links to developers | started_atdate & time | finished_atdate & time | statustext |
|---|---|---|---|---|---|---|---|
| 1 | 1 | staging | 080dbe14c1f7 | NULL | 2024-02-11 13:53:27 | 2024-02-11 13:57:27 | success |
| 2 | 1 | production | 080dbe14c1f7 | NULL | 2024-02-11 22:05:27 | 2024-02-11 22:18:27 | success |
| 3 | 1 | staging | 4c82242dfcc9 | 24 | 2024-04-15 16:47:21 | 2024-04-15 17:26:21 | success |
| 4 | 1 | production | 4c82242dfcc9 | 24 | 2024-04-16 20:34:21 | 2024-04-16 20:49:21 | success |
| 5 | 1 | staging | c72d585d663c | 62 | 2024-06-12 15:57:15 | 2024-06-12 16:26:15 | success |
| 6 | 1 | production | c72d585d663c | 58 | 2024-06-13 19:11:15 | 2024-06-13 19:49:15 | failed |
- deployment_idnumber · unique id
- 1
- repository_idnumber · links to repositories
- 1
- environmenttext
- staging
- commit_shatext · links to commits
- 080dbe14c1f7
- triggered_by_idnumber · links to developers
- NULL
- started_atdate & time
- 2024-02-11 13:53:27
- finished_atdate & time
- 2024-02-11 13:57:27
- statustext
- success
- deployment_idnumber · unique id
- 2
- repository_idnumber · links to repositories
- 1
- environmenttext
- production
- commit_shatext · links to commits
- 080dbe14c1f7
- triggered_by_idnumber · links to developers
- NULL
- started_atdate & time
- 2024-02-11 22:05:27
- finished_atdate & time
- 2024-02-11 22:18:27
- statustext
- success
- deployment_idnumber · unique id
- 3
- repository_idnumber · links to repositories
- 1
- environmenttext
- staging
- commit_shatext · links to commits
- 4c82242dfcc9
- triggered_by_idnumber · links to developers
- 24
- started_atdate & time
- 2024-04-15 16:47:21
- finished_atdate & time
- 2024-04-15 17:26:21
- statustext
- success
- deployment_idnumber · unique id
- 4
- repository_idnumber · links to repositories
- 1
- environmenttext
- production
- commit_shatext · links to commits
- 4c82242dfcc9
- triggered_by_idnumber · links to developers
- 24
- started_atdate & time
- 2024-04-16 20:34:21
- finished_atdate & time
- 2024-04-16 20:49:21
- statustext
- success
- deployment_idnumber · unique id
- 5
- repository_idnumber · links to repositories
- 1
- environmenttext
- staging
- commit_shatext · links to commits
- c72d585d663c
- triggered_by_idnumber · links to developers
- 62
- started_atdate & time
- 2024-06-12 15:57:15
- finished_atdate & time
- 2024-06-12 16:26:15
- statustext
- success
- deployment_idnumber · unique id
- 6
- repository_idnumber · links to repositories
- 1
- environmenttext
- production
- commit_shatext · links to commits
- c72d585d663c
- triggered_by_idnumber · links to developers
- 58
- started_atdate & time
- 2024-06-13 19:11:15
- finished_atdate & time
- 2024-06-13 19:49:15
- statustext
- failed
first 6 of 8,991 rows · your queries use all of them
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.