The Fast Lane

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Travelport

Problem

The data platform team is benchmarking the pipelines that finish quickly, those that complete in 45 minutes or less (2700 seconds). For each of those pipelines, find the average rows output, sorted from the highest average to the lowest.

Only the quick runs make the cut. Measure what they move.

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 is

Strip the benchmarking language and this is a filtered group average wearing a costume. Keep the runs that finished inside the time window, bucket them by pipeline name, average rows_out, and sort. The whole problem turns on one quiet decision the prompt never spells out: what happens to the runs where dur_secs is NULL. A run that never recorded a duration is not a fast run, and that is exactly where candidates either quietly do the right thing or quietly break it.

Building it

Step 1: Filter to the window

Cut to the fast runs with dur_secs BETWEEN 0 AND 2700. The lower bound of 0 keeps the intent honest (a duration cannot be negative) and the upper bound is the 45-minute ceiling, inclusive, so a run at exactly 2700 seconds counts. NULL durations fall away for free, no explicit IS NOT NULL needed.

Step 2: Bucket by pipeline

Collapse the surviving runs by pipe_name so each pipeline becomes one row in the output. pipe_id is unique per run, so grouping by it would give one row per run.

Step 3: Average and sort

Take AVG(rows_out) per bucket. AVG skips NULL rows_out automatically, so a run that logged no output count does not drag the average toward zero, it simply does not participate. Order by that average, highest first.

The solution

SELECT pipe_name, AVG(rows_out) AS avg_rows_out
FROM data_pipes
WHERE dur_secs BETWEEN 0 AND 2700
GROUP BY pipe_name
ORDER BY avg_rows_out DESC

Looks careful, is wrong

COALESCE(dur_secs, 0) BETWEEN 0 AND 2700 pulls every NULL-duration run into the fast set, adding etl_orders and agg_weekly with averages built from runs that never reported how long they took.

Correct

Plain dur_secs BETWEEN 0 AND 2700 lets NULL durations evaluate to NULL and drop out, so only genuinely fast runs reach the average.

Common follow-up questions

• How would you flag pipelines whose average output is trending down over consecutive runs? (Tests a LAG window function to compare each run against the pipeline's prior run.)
• If the metric were rows per second instead of total rows, how would the query change? (Tests whether the candidate computes a true rate (`rows_out` / `dur_secs`) and guards the divisor with NULLIF.)
• What if some pipelines legitimately process zero rows on a given run? (Tests judgment about including or excluding zero-output runs and what that does to the ranking.)