What Never Arrives

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

mid

Asked in

Verizon

Problem

Each ingestion job logs its runs in data_pipes and stamps its own status, so a run that finished shows up as 'success' in whatever mix of upper and lower case that job uses. The data platform team wants every pipeline with a successful run and the records it lost across those runs between what it read and what it wrote, biggest losses first. A run missing either count says nothing about its loss, and a pipeline whose successful runs all miss one still belongs at the bottom with its loss left blank.

Every run reads more than it delivers. Find the pipelines bleeding the most between the door and the destination.

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 really is

This is a per-group sum of a derived quantity dressed up as an ingestion audit. Anyone can write the GROUP BY and the SUM. What separates candidates is normalizing status before the aggregate runs. The jobs stamp both 'success' and 'Success', and an exact match against one spelling keeps only some of the finished runs. On this data the cost is stark: every lowercase 'success' run is missing rows_in, so WHERE status = 'success' hands back a blank loss for every pipeline. The query runs without error and the audit is empty.

Break down the requirements

Step 1: Scope to finished runs, ignoring case

status mixes 'Success' and 'success' alongside 'failed', 'FAILED', 'running', 'queued' and 'skipped'. LOWER(status) = 'success' keeps both spellings of a finished run and nothing else. The normalization belongs on the column, because the column is where the mixed casing lives.

Step 2: Subtract inside the aggregate

A run's loss is rows_in - rows_out, and SUM(rows_in - rows_out) totals it per pipeline. If either count is missing, the difference is NULL and SUM skips it, which is exactly what the statement asks for: such a run says nothing about loss. Do not substitute zero for the missing counts. A missing rows_in would then subtract that run's whole rows_out.

Step 3: Keep the pipeline with no usable counts

One pipeline has successful runs, but each of them is missing a count, so its SUM is NULL rather than 0. The group still exists, so it stays in the result, and ORDER BY records_dropped DESC puts that blank after every real number. Filtering incomplete runs out in WHERE would make the pipeline vanish, and the statement rules that out.

The solution

SELECT pipe_name,
       SUM(rows_in - rows_out) AS records_dropped
FROM data_pipes
WHERE LOWER(status) = 'success'
GROUP BY pipe_name
ORDER BY records_dropped DESC

Exact match

WHERE status = 'success' keeps only the lowercase runs, and on this data none of them logged rows_in. Every pipeline comes back with a blank loss.

Normalized match

WHERE LOWER(status) = 'success' keeps 'Success' and 'success' alike, so the 'Success' runs that logged both counts reach the sum.

Common follow-up questions

• Now show only pipelines whose total loss is above 10,000 records. (Tests moving a post-aggregate threshold into `HAVING SUM(rows_in - rows_out) > 10000` rather than a `WHERE` clause.)
• Report the drop as a percentage of rows read, not an absolute count. (Tests guarding a divide-by-zero when `SUM(rows_in)` is zero and casting to `REAL` for a fractional rate.)
• How would you make this incremental so a dashboard only recomputes the last day of runs? (Tests a `start_at` partition predicate and reasoning about partition pruning on the large table.)