Root Cause of Pipeline Failures - SQL Interview Question

Root Cause of Pipeline FailuresCompany: Gitlab | Difficulty: hard

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. Sort the results by project in descending order, breaking ties by author in descending order.

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	gitlab-runner	Nadia Petrov	2023-01-01 09:00:00
2	gitlab-runner	Nadia Petrov	2023-01-02 09:00:00
3	gitlab-runner	Callum Reyes	2023-01-03 09:00:00
4	gitlab-runner	Callum Reyes	2023-01-04 09:00:00

Example Input: `pipelines`

pipeline_id	project	commit_id	status	pipeline_time
1	gitlab-runner	1	failed	2023-01-01 09:10:00
2	gitlab-runner	2	failed	2023-01-02 09:10:00
3	gitlab-runner	3	failed	2023-01-03 09:10:00
4	gitlab-runner	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

Nadia Petrov's commits (1, 2) triggered pipelines 1 and 2, both failed with an ERROR log entry, so both are attributed to her — 2 total. Callum Reyes's commit 3 triggered pipeline 3, which failed, but its only log entry is WARN, not ERROR, so it is not attributed. Callum Reyes's commit 4 triggered a success pipeline, so it's never attributed regardless of logs. Nadia Petrov has 2 attributed failures and Callum Reyes has 0, so Nadia Petrov is the top author for project gitlab-runner.

SchemaDROP 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, 'gitlab-runner', 'Nadia Petrov', '2023-01-01 09:00:00'), (2, 'gitlab-runner', 'Nadia Petrov', '2023-01-02 09:00:00'), (3, 'gitlab-runner', 'Callum Reyes', '2023-01-03 09:00:00'), (4, 'gitlab-runner', 'Callum Reyes', '2023-01-04 09:00:00'); INSERT INTO pipelines VALUES (1, 'gitlab-runner', 1, 'failed', '2023-01-01 09:10:00'), (2, 'gitlab-runner', 2, 'failed', '2023-01-02 09:10:00'), (3, 'gitlab-runner', 3, 'failed', '2023-01-03 09:10:00'), (4, 'gitlab-runner', 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');← All SQL Challenges · Gitlab Questions · Learn SQL Free