Databricks' platform operations team wants to compare pipeline reliability across workspaces during February 2025. For every pipeline with at least two runs started during February 2025, report its workspace, region, owning team, total runs, failed runs, SLA breaches, and average completed-run duration. A run is an SLA breach when its completed duration is strictly greater than the pipeline's `sla_minutes` target. Unresolved runs (`finished_at` is `NULL`) must be included in `total_runs` but excluded from duration averages and SLA-breach counts. Return the columns in this exact order: `workspace_name`, `region_name`, `pipeline_name`, `team_name`, `total_runs`, `failed_runs`, `sla_breaches`, and `average_duration_minutes`. Sort the results by `workspace_name` in descending order, then by `pipeline_name` in descending order. The solution must combine the six tables using exactly five joins. ### Table Schema | Table | Column | Type | |---|---|---| | `pipeline_runs` | `run_id` | integer | | `pipeline_runs` | `pipeline_id` | integer | | `pipeline_runs` | `workspace_id` | integer | | `pipeline_runs` | `started_at` | timestamp | | `pipeline_runs` | `finished_at` | timestamp | | `pipeline_runs` | `status` | varchar(20) | | `pipelines` | `pipeline_id` | integer | | `pipelines` | `pipeline_name` | varchar(60) | | `pipelines` | `owner_id` | integer | | `pipelines` | `sla_minutes` | integer | | `workspaces` | `workspace_id` | integer | | `workspaces` | `workspace_name` | varchar(60) | | `workspaces` | `region_id` | integer | | `regions` | `region_id` | integer | | `regions` | `region_name` | varchar(40) | | `owners` | `owner_id` | integer | | `owners` | `team_id` | integer | | `teams` | `team_id` | integer | | `teams` | `team_name` | varchar(60) | ### Example Input: `pipeline_runs` | run_id | pipeline_id | workspace_id | started_at | finished_at | status | |---:|---:|---:|---|---|---| | 1 | 101 | 10 | 2025-02-01 08:00:00 | 2025-02-01 08:20:00 | succeeded | | 2 | 101 | 10 | 2025-02-02 08:00:00 | 2025-02-02 08:40:00 | failed | | 3 | 101 | 10 | 2025-02-03 08:00:00 | 2025-02-03 08:10:00 | succeeded | | 4 | 102 | 10 | 2025-02-01 09:00:00 | 2025-02-01 09:05:00 | succeeded | | 5 | 102 | 10 | 2025-02-02 09:00:00 | 2025-02-02 09:10:00 | succeeded | ### Example Input: `pipelines` | pipeline_id | pipeline_name | owner_id | sla_minutes | |---:|---|---:|---:| | 101 | Customer Events | 501 | 30 | | 102 | Billing Export | 502 | 15 | ### Example Input: `workspaces` | workspace_id | workspace_name | region_id | |---:|---|---:| | 10 | Production Lakehouse | 1 | ### Example Input: `regions` | region_id | region_name | |---:|---| | 1 | US-East | ### Example Input: `owners` | owner_id | team_id | |---:|---:| | 501 | 601 | | 502 | 602 | ### Example Input: `teams` | team_id | team_name | |---:|---| | 601 | Data Platform | | 602 | Finance Engineering | ### Example Output Explanation Customer Events has three runs, one failed run, and one run lasting longer than its 30-minute SLA. Its average completed duration is 23.33 minutes. Billing Export has two successful runs and no SLA breaches. Both pipelines qualify because each has at least two February runs.
DROP TABLE IF EXISTS pipeline_runs; DROP TABLE IF EXISTS pipelines; DROP TABLE IF EXISTS workspaces; DROP TABLE IF EXISTS regions; DROP TABLE IF EXISTS owners; DROP TABLE IF EXISTS teams; CREATE TABLE pipeline_runs ( run_id INT, pipeline_id INT, workspace_id INT, started_at TIMESTAMP, finished_at TIMESTAMP, status VARCHAR(20) ); CREATE TABLE pipelines ( pipeline_id INT, pipeline_name VARCHAR(60), owner_id INT, sla_minutes INT ); CREATE TABLE workspaces ( workspace_id INT, workspace_name VARCHAR(60), region_id INT ); CREATE TABLE regions ( region_id INT, region_name VARCHAR(40) ); CREATE TABLE owners ( owner_id INT, team_id INT ); CREATE TABLE teams ( team_id INT, team_name VARCHAR(60) ); INSERT INTO pipeline_runs VALUES (1,101,10,'2025-02-01 08:00:00','2025-02-01 08:20:00','succeeded'), (2,101,10,'2025-02-02 08:00:00','2025-02-02 08:40:00','failed'), (3,101,10,'2025-02-03 08:00:00','2025-02-03 08:10:00','succeeded'), (4,102,10,'2025-02-01 09:00:00','2025-02-01 09:05:00','succeeded'), (5,102,10,'2025-02-02 09:00:00','2025-02-02 09:10:00','succeeded'); INSERT INTO pipelines VALUES (101,'Customer Events',501,30), (102,'Billing Export',502,15); INSERT INTO workspaces VALUES (10,'Production Lakehouse',1); INSERT INTO regions VALUES (1,'US-East'); INSERT INTO owners VALUES (501,601), (502,602); INSERT INTO teams VALUES (601,'Data Platform'), (602,'Finance Engineering');