# Where the Rows Go > Every batch reads more than it writes. Find the pipelines quietly losing the most on the way through. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: mid ## Problem Our ingestion pipelines read raw records and write cleaned ones, and each job stamps its own run status, so a run that finished shows up as 'success' in whatever mix of upper and lower case that job happens to use. Across the runs that finished successfully, find the pipelines losing the most records between what they read and what they wrote, biggest losses first. ## Worked solution and explanation ### Why this problem exists in real interviews This is a per-group sum of a derived quantity, dressed up as a data-ingestion audit. The real skill being probed: can you sum a per-row difference (rows_in - rows_out) at the pipeline grain while scoping to only the runs that actually completed? Anyone can GROUP BY and SUM. The two things that separate candidates are summing the difference at the right grain and normalizing the status casing so 'Success' and 'success' land in the same bucket. Miss the casing and half your successful runs silently disappear, and the drop totals come out too small to trust. --- ### Break down the requirements #### Step 1: Scope to completed runs, case-insensitively The status column mixes 'Success' and 'success' (and separately 'FAILED', 'running', 'queued'). WHERE LOWER(status) = 'success' keeps both casings of the completed runs and drops everything else. A raw status = 'success' would quietly exclude every 'Success' row. #### Step 2: Compute the drop per row, then sum Each run's loss is rows_in - rows_out. Sum that expression inside the aggregate: SUM(rows_in - rows_out). This is algebraically the same as SUM(rows_in) - SUM(rows_out), so either form is correct, but keeping the subtraction inside reads as 'total records dropped'. #### Step 3: Roll up to the pipeline and sort GROUP BY pipe_name collapses all of a pipeline's successful runs into one row, and ORDER BY records_dropped DESC puts the biggest offenders on top where an on-call engineer would look first. --- ### The solution **Records dropped per pipeline** ```sql 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 ``` > **Interviewers Watch For** > > Whether you actually act on the casing warning instead of trusting one spelling of 'success'. The prompt tells you each job stamps status differently, and a strong candidate turns that into LOWER(status) = 'success' rather than filtering on a single casing and quietly losing half the completed runs. Bonus points for asking whether rows_out can exceed rows_in (enrichment steps that add rows), which would make records_dropped negative and pull a pipeline to the bottom of the list. > **Common Pitfall** > > Writing WHERE status = 'success' and silently dropping every 'Success' run. It passes on a clean fixture and understates the totals the moment real logs arrive with inconsistent casing. Normalize in the filter, not by hoping the data is uniform. > **Cost Analysis** > > At interview scale this is one table of a few hundred rows. In production picture an ingestion-metrics table at 200M+ run records partitioned by start_at: the LOWER(status) predicate defeats a plain index on status, so add a functional index on LOWER(status) or store a normalized status_lc column at write time. The aggregation itself is a single hash-group pass, cheap even at scale. --- ## Common follow-up questions - Now show only pipelines that dropped more than they should, say a total loss 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where-the-rows-go) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.