Where The Rows Go

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

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. Some runs never captured an output count and store -1 in its place; those runs have no meaningful ratio, so leave it empty.

Every run loses a little on the way. Measure what makes it through.

Tables

data_pipes

Columns: pipe_id INTEGER, pipe_name TEXT, rows_in INTEGER, rows_out INTEGER, start_at TEXT, status TEXT, dur_secs INTEGER

Expected output

Worked solution and explanation

A sentinel hiding in the numerator

Beneath the throughput-metric costume this is two small correctness traps stacked on a trivial SELECT. First, rows_out / rows_in is integer division in SQL: 97/137 comes back as 0, not 0.708, so every sub-1 ratio silently collapses to zero. Second, a run that never recorded its output stores -1 in rows_out, and left alone that -1 divides into a small negative ratio that looks like real data. Anyone can write the SELECT and the ORDER BY; what separates candidates is forcing float division with * 1.0 and blanking the -1 placeholder with NULLIF so the uncomputable runs read empty instead of wrong. 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 what keeps every sub-1 ratio from collapsing to zero.

Step 2: Neutralize the placeholder

A run that never captured an output count stores -1 in rows_out. Treated as a real number it produces a negative ratio, so wrap the numerator in NULLIF(rows_out, -1): the -1 becomes NULL, the division yields NULL, and the run stays in the output with a blank ratio. Genuinely missing (NULL) read or write counts propagate to a blank ratio on their own.

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

SELECT
    pipe_name,
    start_at,
    ROUND(NULLIF(rows_out, -1) * 1.0 / rows_in, 4) AS throughput_ratio
FROM data_pipes
ORDER BY pipe_name, start_at

Placeholder left in (wrong)

ROUND(rows_out / rows_in, 4) Integer division makes 97/137 = 0, so every ratio under 1 becomes 0.0000, and the -1 rows compute -1 divided by rows_in, a tiny negative ratio. The query returns rows and looks fine.

Guarded (correct)

ROUND(NULLIF(rows_out, -1) * 1.0 / rows_in, 4) 97 * 1.0 / 137 = 0.708029..., rounded to 0.7080, and a -1 output becomes NULL, so unrecorded runs read as empty instead of impossible.

Common follow-up questions

• This runs in SQLite, where dividing by zero yields NULL. PostgreSQL raises an error instead. How would you guard `rows_in` there, and would you still need the `* 1.0`? (Tests whether they know division and zero-handling are engine-specific.)
• How would you extend this to report each pipeline's average throughput ratio across all of its runs, ignoring the unrecorded ones? (Probes aggregation over a null-bearing expression.)
• How would you return only the runs whose throughput ratio fell below 0.5, keeping the blank-ratio runs out of that list? (Tests filtering on a computed, null-bearing expression.)