# Where The Rows Go > Every run loses a little on the way. Measure what makes it through. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L5 ## Problem The data platform team measures how efficiently each pipeline run turns the rows it reads into the rows it writes back out. For every run, show the pipeline name, its start time, and the ratio of rows written to rows read to four decimal places, ordered by pipeline name and then start time. ## Worked solution and explanation ### A precision trap wearing a metrics costume This looks like a friendly SELECT-and-order warmup, and the SELECT list is the easy part. The real question is whether you know that `rows_out / rows_in` is integer division in SQL: 97 / 137 comes back as 0, not 0.708, so every ratio below 1 silently collapses to zero. Beat that with `* 1.0`, then survive the runs where `rows_in` is 0 or NULL without erroring or dropping the row. Miss either and the query still runs, which is exactly why it is dangerous. --- ### Build it in three moves #### Step 1: Force real division Multiply `rows_out` by 1.0 before dividing so the engine switches to floating-point. `rows_out * 1.0 / rows_in` gives 0.7080; `rows_out / rows_in` gives 0. This single `* 1.0` is the whole problem. #### Step 2: Protect the denominator Some runs have `rows_in` of 0 (an empty run) or NULL (never recorded). Wrap the denominator in `NULLIF(rows_in, 0)` so a zero denominator becomes NULL instead of a divide-by-zero, and a NULL numerator or denominator naturally yields a NULL ratio. The run stays in the output with a blank ratio. #### Step 3: Project and order Project `pipe_name`, `start_at`, and the rounded ratio, then order by `pipe_name, start_at` so runs of the same pipeline read chronologically. --- ### The solution **Per-run throughput ratio** ```sql SELECT pipe_name, start_at, ROUND(rows_out * 1.0 / NULLIF(rows_in, 0), 4) AS throughput_ratio FROM data_pipes ORDER BY pipe_name, start_at ``` > **Trick to solving** > > The `* 1.0` is doing the heavy lifting: it promotes an integer division to floating-point. `CAST(rows_out AS REAL)` works too. Whichever you pick, it must sit BEFORE the division, not around the whole expression, or the truncation has already happened. **Integer division (wrong)** ROUND(rows_out / rows_in, 4) For 97/137 the engine computes the integer quotient 0, then rounds 0 to 0.0000. Every ratio under 1 becomes 0 and the query looks fine. **Float division (correct)** ROUND(rows_out * 1.0 / NULLIF(rows_in, 0), 4) 97 * 1.0 / 137 = 0.708029..., rounded to 0.7080, and a 0 denominator is turned into NULL instead of crashing. > **Common pitfall** > > The mistake that fails silently is omitting `* 1.0`: the query runs, returns rows, and reports a wall of 0.0000 ratios that no one notices until a dashboard looks broken. The mistake that fails loudly is dividing by a 0-row run; `NULLIF` turns that into a clean NULL. > **Performance insight** > > With `data_pipes` at 50,000 rows this is a single sequential scan plus a sort for the ORDER BY; no aggregation, no join, so cost is dominated by the sort. At much larger volume a covering index on (pipe_name, start_at) lets the engine return rows already ordered and skip the sort. --- ## Common follow-up questions - This runs in SQLite. How would the division behave differently in PostgreSQL, and would you still need the * 1.0? _(Tests whether they recognize integer division as engine-specific behavior.)_ - How would you extend this to report each pipeline's average throughput ratio across all of its runs? _(Probes aggregation on top of the per-run result.)_ - How would you return only the runs whose throughput ratio fell below 0.5, keeping the NULL-ratio runs out of that list? _(Tests filtering on a computed, null-bearing expression.)_ ## 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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.