Pipeline Completion Rate

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Meta

Problem

Each run in data_pipes logs how many rows it read in and how many it wrote out, but the table mixes tiny test runs and errored runs (which record a rows_out of -1) in with real production work. Counting a run as production only when it wrote out at least 3,000 rows, report each pipeline's average input rows across its production runs.

How far do users get through the flow?

Tables

data_pipes

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

Expected output

Worked solution and explanation

What this problem is really about

Strip the framing and this is a grouped average with a population problem in front of it: the average is only meaningful once you decide which runs belong in it. The data_pipes log throws tiny test runs and errored runs (the ones logging rows_out of -1) into the same table as real production work. Anyone can write GROUP BY and AVG. The skill being probed is whether you scope the population first, or average everything and quietly report a number that is too low.

Why a plain average lies here

The -1 sentinel is not NULL, so AVG gives it no free pass: an errored run still carries a real rows_in, and averaging that in with the genuine production runs drags each pipeline's mean down. The small test runs do the same, and they are not even dirty data, just runs that were never meant to count. Filter neither out and every pipeline reports an average input size well below its true production figure. The fix is one predicate that removes both classes of noise at once.

Building the query

Step 1: Scope the population to production runs

WHERE rows_out >= 3000 keeps only production runs. Because -1 is far below 3,000, this same predicate discards the errored runs, and because small test runs also fall short, it discards them too: one filter, both problems solved. No status check is needed.

Step 2: Group by pipeline

GROUP BY pipe_name collapses each pipeline's surviving runs into a single row so the average is taken per pipeline.

Step 3: Average the input rows

AVG(rows_in) averages the input size across the production runs that cleared the filter, giving each pipeline its typical production batch size.

SELECT
    pipe_name,
    AVG(rows_in) AS avg_input_rows
FROM data_pipes
WHERE rows_out >= 3000
GROUP BY pipe_name

Averages the whole log

No filter. Errored -1 runs and small test runs are folded in, so a pipeline like export_s3 reports roughly 6,900 average input rows, low enough to look plausible, which is what makes it dangerous.

Scopes first, then averages

WHERE rows_out >= 3000 keeps only production runs, and export_s3 reports its true ~11,097. The answers diverge on every pipeline, because every pipeline has noise runs pulling its unfiltered mean down.

Common follow-up questions

• Where does the 3,000 cutoff come from, and how would you handle a run that genuinely wrote out 2,999 rows? (Tests whether the candidate distinguishes a legitimate low-output run from the -1 sentinel and can defend the threshold.)
• How would the query change if the business wanted average OUTPUT per production run instead of average input? (Tests understanding that a per-run average and a pooled ratio answer different questions.)
• If new error sentinels appeared over time (say -1 and -999), how would you keep this filter robust to them? (Tests operational awareness around evolving sentinel encodings.)