Annual Pipeline Failures

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

City and County of San Francisco

Problem

Run status in data_pipes is not written consistently, so the same outcome can show up in different letter case. Count the failed runs per year for the annual reliability review, looking only at runs that recorded a runtime, and list the years from earliest to latest.

How many pipelines broke this year?

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

This is a filtered count over a dirty status column dressed up as a reliability review. The yearly GROUP BY is the part everyone gets right. The real exam is hidden in the data: status stores the same outcome as 'failed' and 'FAILED', and some runs never recorded a dur_secs at all. The candidates who separate themselves notice that before writing a single clause. Treat status as clean and every year comes back short; count the untimed runs and the latest year swells with failures that never recorded a runtime.

The trap, made concrete

Look at the sample rows: pipe 131 is 'failed' but pipe 286 is 'FAILED'. A filter of status = 'failed' quietly drops 286 and your yearly counts come back too low, with no error to warn you. That silent undercount is exactly the kind of bug that ships to a reliability dashboard and gets trusted for months. Normalizing the case is not optional polish here, it is correctness.

Step 1: Filter on a normalized status

Normalize the status before comparing. LOWER(status) = 'failed' catches both 'failed' and 'FAILED' in one predicate, so no uppercase failure slips through.

Step 2: Drop runs that never executed

Add dur_secs IS NOT NULL. A run that never recorded a runtime did not execute a measurable job, so it is not a genuine execution failure. On this data every one of those untimed failures lands in the latest year, so leaving them in inflates exactly the year a review reads first.

Step 3: Bucket by year and count

Derive the year with strftime('%Y', start_at) and group on that expression. Each surviving failure rolls up into its year, and COUNT(*) tallies the rows per year.

Step 4: Order chronologically

Order by run_year ascending so the timeline reads from the earliest year to the latest, which is how a reliability review scans it top to bottom.

The solution

SELECT
    strftime('%Y', start_at) AS run_year,
    COUNT(*) AS failed_runs
FROM data_pipes
WHERE LOWER(status) = 'failed'
  AND dur_secs IS NOT NULL
GROUP BY run_year
ORDER BY run_year ASC

Naive: `status = 'failed'`

Matches only the lowercase rows. Pipe 286 ('FAILED') is dropped, so every year's count is silently too low and no error fires.

Correct: `LOWER(status) = 'failed'`

Folds both casings into one bucket before counting, so the yearly totals reflect every real failure.

Common follow-up questions

• Now break the failed-run counts down by `pipe_name` as well as year. What changes in the grouping and the output shape? (Tests whether the candidate can add a second grouping dimension without double counting.)
• If a year had zero failures, it simply will not appear. How would you return a row of 0 for every year in the range instead? (Tests understanding of dense vs sparse time series and gap filling.)
• At 500M rows, wrapping `status` in `LOWER()` can block an index. How would you keep the status filter efficient? (Tests awareness that a function on a column can defeat an index and how to keep the filter sargable at scale.)