GitLab's CI Reliability team wants to identify, for each project, which author's commits are most responsible for triggering **attributed pipeline failures** — genuine build breakages, not flaky infrastructure noise. A pipeline failure only counts as 'attributed' to its commit author if **both** of the following are true: - the pipeline's `status` is `'failed'`, AND - the pipeline has at least one entry in `pipeline_logs` with `log_level = 'ERROR'` (A pipeline that failed but has no `ERROR` log entries is treated as an infrastructure fluke, and a pipeline that ultimately succeeded is never attributed as a failure — even if a job logged an `ERROR` along the way, for example during an automatic retry.) For each project, count how many *distinct* attributed-failure pipelines each author is responsible for (a pipeline should only be counted once toward an author's total, even if it has multiple `ERROR` log entries). Then find the author (or authors, in case of a tie) with the highest count of attributed failures within that project. Only include projects that have at least one attributed failure at all — a project where nothing is attributable to anyone should not appear in the output. Output the project, the author's name, and their attributed failure count. ### Table Schema | Table | Column | Type | |---|---|---| | `commits` | `commit_id` | integer | | `commits` | `project` | string | | `commits` | `author` | string | | `commits` | `commit_time` | timestamp | | `pipelines` | `pipeline_id` | integer | | `pipelines` | `project` | string | | `pipelines` | `commit_id` | integer | | `pipelines` | `status` | string ('success' or 'failed') | | `pipelines` | `pipeline_time` | timestamp | | `pipeline_logs` | `log_id` | integer | | `pipeline_logs` | `pipeline_id` | integer | | `pipeline_logs` | `log_level` | string ('INFO', 'WARN', 'ERROR') | | `pipeline_logs` | `message` | string | | `pipeline_logs` | `logged_at` | timestamp | ### Example Input: `commits` | commit_id | project | author | commit_time | |---|---|---|---| | 1 | Alpha | Alice | 2023-01-01 09:00:00 | | 2 | Alpha | Alice | 2023-01-02 09:00:00 | | 3 | Alpha | Bob | 2023-01-03 09:00:00 | | 4 | Alpha | Bob | 2023-01-04 09:00:00 | ### Example Input: `pipelines` | pipeline_id | project | commit_id | status | pipeline_time | |---|---|---|---|---| | 1 | Alpha | 1 | failed | 2023-01-01 09:10:00 | | 2 | Alpha | 2 | failed | 2023-01-02 09:10:00 | | 3 | Alpha | 3 | failed | 2023-01-03 09:10:00 | | 4 | Alpha | 4 | success | 2023-01-04 09:10:00 | ### Example Input: `pipeline_logs` | log_id | pipeline_id | log_level | message | logged_at | |---|---|---|---|---| | 1 | 1 | ERROR | build failed: syntax error | 2023-01-01 09:12:00 | | 2 | 2 | ERROR | test failed: assertion error | 2023-01-02 09:12:00 | | 3 | 3 | WARN | deprecated api used | 2023-01-03 09:12:00 | ### Example Output Explanation Alice's commits (1, 2) triggered pipelines 1 and 2, both `failed` with an `ERROR` log entry, so both are attributed to her — 2 total. Bob's commit 3 triggered pipeline 3, which `failed`, but its only log entry is `WARN`, not `ERROR`, so it is not attributed. Bob's commit 4 triggered a `success` pipeline, so it's never attributed regardless of logs. Alice has 2 attributed failures and Bob has 0, so Alice is the top author for project Alpha.
DROP TABLE IF EXISTS pipeline_logs; DROP TABLE IF EXISTS pipelines; DROP TABLE IF EXISTS commits; CREATE TABLE commits (commit_id INT, project VARCHAR(100), author VARCHAR(100), commit_time TIMESTAMP); CREATE TABLE pipelines (pipeline_id INT, project VARCHAR(100), commit_id INT, status VARCHAR(20), pipeline_time TIMESTAMP); CREATE TABLE pipeline_logs (log_id INT, pipeline_id INT, log_level VARCHAR(10), message VARCHAR(255), logged_at TIMESTAMP); INSERT INTO commits VALUES (1, 'Alpha', 'Alice', '2023-01-01 09:00:00'), (2, 'Alpha', 'Alice', '2023-01-02 09:00:00'), (3, 'Alpha', 'Bob', '2023-01-03 09:00:00'), (4, 'Alpha', 'Bob', '2023-01-04 09:00:00'); INSERT INTO pipelines VALUES (1, 'Alpha', 1, 'failed', '2023-01-01 09:10:00'), (2, 'Alpha', 2, 'failed', '2023-01-02 09:10:00'), (3, 'Alpha', 3, 'failed', '2023-01-03 09:10:00'), (4, 'Alpha', 4, 'success', '2023-01-04 09:10:00'); INSERT INTO pipeline_logs VALUES (1, 1, 'ERROR', 'build failed: syntax error', '2023-01-01 09:12:00'), (2, 2, 'ERROR', 'test failed: assertion error', '2023-01-02 09:12:00'), (3, 3, 'WARN', 'deprecated api used', '2023-01-03 09:12:00');