The Weight of a Verdict

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

Zenefits

Problem

Each pipeline run in data_pipes gets benchmarked by the data platform team against every run sharing its status, using the total rows ingested across that status (a status whose runs never recorded an ingest count has no benchmark). Return every run with its original columns intact, alongside that status-wide total.

Every run carries the full measure of all who ended the same way.

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

This is grain preservation dressed up as benchmarking. Totalling rows_in per status is the easy half. The real skill is attaching that total to every run without letting the status collapse the runs. Aggregate the table outright and 200 runs fold into seven status rows: the numbers are right, and every column the team asked to keep is gone.

Build it

Step 1: Read the grain off the ask

'Return every run with its original columns intact' fixes the output at one row per run. The benchmark belongs to the group, but it is reported at the run's grain, so it rides along as one extra column.

Step 2: Total rows_in over the run's whole status

SUM(rows_in) OVER (PARTITION BY status) adds up every run sharing the current row's status and writes that sum onto each of them. There is no ORDER BY inside the window, so the frame is the entire status, not a running prefix.

Step 3: Take status exactly as stored

The seed spells some statuses two ways, and each spelling is its own status: the preview shows 'FAILED' at 197554 and 'failed' at 205500. Partitioning on the raw value is what the grader expects.

Step 4: Let missing ingest counts fall out

SUM skips NULLs, so the 28 runs without a rows_in add nothing. Every one of them is a 'success' run, which leaves 'success' with no recorded counts at all: its benchmark comes back NULL, while 'Success' gets a real total. That is the empty benchmark the statement describes, not a bug.

SELECT
    pipe_id,
    pipe_name,
    status,
    rows_in,
    rows_out,
    start_at,
    dur_secs,
    SUM(rows_in) OVER (PARTITION BY status) AS status_total_rows_in
FROM data_pipes

Common follow-up questions

• What if the team wanted 'success' and 'Success' benchmarked as one status? (Partition by `LOWER(status)` while still returning the raw `status`; 'success' stops being empty because 'Success' brings counts.)
• How would you show 0 instead of an empty benchmark for a status with no ingest counts? (Wrap the window in `COALESCE(..., 0)`, and say why coalescing the result differs from treating each missing count as zero.)
• How would you add each run's share of its status total? (Divide `rows_in` by the same window, guarding the NULL total and avoiding integer division.)
• What if you needed a running total within each status by start time? (Add `ORDER BY start_at` to the window and decide how ties on `start_at` are framed (`ROWS` versus `RANGE`).)