Successful Pipeline Runs

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Spotify

Problem

In data_pipes, which logs every pipeline execution, a run counts as successful only when its status is exactly the lowercase 'success'; any other capitalization of that word is not a completed run. List each pipeline with at least one such run alongside its number of successful runs, most successes first.

Which pipelines completed successfully?

Tables

data_pipes

Columns: pipe_id INTEGER, pipe_name TEXT, status TEXT, rows_in INTEGER, rows_out INTEGER, start_at TEXT, dur_secs INTEGER

data_pipes: 10 of 200 rows
pipe_id	pipe_name	status	rows_in	rows_out	start_at	dur_secs
131	etl_users	failed	137	97	2026-02-02 01:00:00	28
162	etl_events	running	274	194	2026-03-03 02:00:00	51
193	sync_crm	queued	411	291	2026-04-04 03:00:00	74
224	agg_daily	skipped	548	388	2026-05-05 04:00:00	97
255	agg_weekly	Success	685	485	2026-06-06 05:00:00	NULL
286	export_s3	FAILED	822	582	2026-07-07 06:00:00	143
317	import_csv	success	NULL	679	2026-08-08 07:00:00	166
348	ml_train	failed	1096	776	2026-09-09 08:00:00	189
379	dbt_build	running	1233	NULL	2026-01-10 09:00:00	212
410	etl_orders	queued	1370	970	2026-02-11 10:00:00	NULL

Expected output

First 5 of 10 rows
pipe_name	success_count
ml_train	4
import_csv	4
etl_users	4
sync_crm	2
export_s3	2

Worked solution and explanation

Why this problem exists in real interviews

This tests whether a candidate can demonstrate writing clean, correct queries under time pressure. This is a foundational check that interviewers use early in a round to verify baseline proficiency.

Break down the requirements

Step 1: Select the target columns

The SELECT clause picks exactly the columns the prompt asks for. Returning extra columns or missing a required alias would fail the grading check.

Step 2: Verify the output shape

Confirm the result has the expected columns, ordering, and no duplicate rows. A quick sanity check on row count catches logic errors before submission.

The solution

Group `data_pipes` by name and count successful completions

SELECT pipe_name, COUNT(*) AS success_count
FROM data_pipes
WHERE status = 'success'
GROUP BY pipe_name
ORDER BY success_count DESC

Common follow-up questions

If `pipe_name` contains trailing whitespace in some rows, how would that affect your GROUP BY result? (Tests awareness of dirty data causing phantom groups; TRIM would collapse them.)
Should you filter with WHERE status = 'success' or use HAVING with a conditional count, and what is the performance difference? (Tests understanding of predicate pushdown; WHERE filters before grouping, reducing work.)
How would you also display each pipeline's average `rows_out` alongside its success count? (Tests whether the candidate can add an aggregate on a different column without breaking the grouping.)