Early 2026 Data Pipelines

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

Problem

Which pipelines in data_pipes have logged at least one run reporting fewer than zero output rows in rows_out? Name each offending pipeline once, however many bad runs it has.

Which pipelines ran before the year turned mid.

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 4 of 4 rows
pipe_name
sync_crm
export_s3
dbt_build
etl_events

Worked solution and explanation

What they're actually testing

Strip the costume and this is a filter at one grain with an answer at another: keep the runs whose rows_out is impossible, then hand back pipeline names without repeats. A negative output count can only come from a broken counter or a bad subtraction upstream, so the question is really 'whose instrumentation is lying?'. The trap is grain. data_pipes is one row per run, the answer is one row per pipeline, and a pipeline with three bad runs will show up three times unless you collapse it deliberately.

Building it

Step 1: Keep only the impossible counts

WHERE rows_out < 0. Most runs report a sane count, and every pipeline has plenty of those, so without this test you get all ten pipelines back. Note what the comparison does with a missing count: NULL < 0 is not true, so runs with no rows_out fall out, which is right. Unknown is not negative.

Step 2: Collapse to one name per pipeline

SELECT DISTINCT pipe_name. The bad runs cluster: a pipeline with a broken counter tends to break more than once. The question asks which pipelines, not how many bad runs, so the dedup is the contract you are answering.

Pipelines that reported an impossible count

SELECT DISTINCT pipe_name
FROM data_pipes
WHERE rows_out < 0;

A row filter on rows_out, then one row per pipe_name.

Over-engineered: WHERE COALESCE(rows_out, -1) < 0 OR LOWER(status) = 'failed'. The default turns every missing count into a fake violation, and the status test drags in runs whose counts were fine.
What the question asks: WHERE rows_out < 0 with DISTINCT on pipe_name. One comparison on the one column that carries the fact, readable in two seconds.

Common follow-up questions

Now return each pipeline's number of bad runs and its worst `rows_out` alongside the name. (Moves from a flat `DISTINCT` to `COUNT(*)` and `MIN(rows_out)` with `GROUP BY pipe_name`.)
Which pipelines wrote more rows out than they read in? (Tests comparing two columns in one row, and what a NULL `rows_in` does to that test.)
How would you list pipelines whose bad runs were all marked successful, given that `status` mixes 'success' and 'Success'? (Introduces normalization plus a group-level condition instead of a row filter.)