Pipeline Overhead by Environment

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

L4

Asked in

EY

Problem

Our data_pipes log records each pipeline run with the rows it read in and wrote out, and we measure a run's overhead as the gap between the two, counting only runs that actually moved data (succeeded, still running, or failed). For every pipeline, report the average overhead alongside the average overhead of just its failed runs, treating status spellings that differ only in capitalization, like 'Success' and 'success', as the same outcome.

Production overhead versus staging.

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 testing

Strip away the dashboard language and this is a conditional aggregation with a dirty key. You need two numbers per pipeline out of a single grouped pass: the overall overhead across every run that moved data, and the overhead of just the failed runs. The tempting shortcut is to write WHERE LOWER(status) = 'failed' to get the failed figure, but that throws away the successful and running runs the overall average is built from. And the status column mixes 'FAILED' with 'failed' and 'Success' with 'success', so if you skip LOWER the mixed-case runs escape both the filter and the failed-run test. Get either wrong and every pipeline's overhead profile is computed over the wrong subset.

Building the query

Step 1: Normalize, filter, and measure in a CTE

In a CTE, normalize and scope once. LOWER(status) folds the casing so 'FAILED' and 'Success' line up with their lowercase twins, and WHERE LOWER(status) IN ('success', 'running', 'failed') keeps only the runs that processed rows, dropping queued and skipped. Compute each run's overhead here too, as ABS(rows_in - rows_out), so the outer query reads one clean column.

Step 2: Aggregate per pipeline, overall and failed-only

Group the CTE by pipe_name. AVG(overhead) gives the overall average across all in-scope runs, and AVG(CASE WHEN outcome = 'failed' THEN overhead END) gives the failed-only average from the same rows: the CASE yields NULL for non-failed runs and AVG skips those, so no second query or join is needed.

The solution

WITH runs AS (
  SELECT
    pipe_name,
    LOWER(status) AS outcome,
    ABS(rows_in - rows_out) AS overhead
  FROM data_pipes
  WHERE LOWER(status) IN ('success', 'running', 'failed')
)
SELECT
  pipe_name,
  AVG(overhead) AS avg_overhead,
  AVG(CASE WHEN outcome = 'failed' THEN overhead END) AS avg_failed_overhead
FROM runs
GROUP BY pipe_name;

Filters the whole query to failures

WHERE LOWER(status) = 'failed' returns the failed average, but now AVG(overhead) is also computed over failures only. The overall column is silently wrong for every pipeline, and there is no successful or running run left to average.

Narrows inside the aggregate

AVG(CASE WHEN outcome = 'failed' THEN overhead END) narrows to failures inside the aggregate, so AVG(overhead) beside it still sees every in-scope run. Both averages come from one scan.

Common follow-up questions

• How would the answer change if the team wanted the absolute difference of the summed totals (`SUM(rows_in)` minus `SUM(rows_out)`) per pipeline instead of the average of per-run gaps? (Tests whether the candidate sees the per-run-ABS-then-average ordering rather than differencing column totals.)
• Extend this to present the pipelines from highest overall `avg_overhead` to lowest, breaking ties on `pipe_name`. (Probes adding a total ordering on top of the aggregate.)
• If a new status like 'paused' appears in `data_pipes`, what happens to it under the current query, and how would you decide whether to include it? (Tests awareness that the IN filter silently excludes any unlisted outcome.)