# The Fast Lane > Only the quick runs make the cut. Measure what they move. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## 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. ## 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. > **The NULL duration trap** > > About 1% of rows have a NULL dur_secs. Written as `dur_secs BETWEEN 0 AND 2700`, the predicate evaluates to NULL for those rows, and a NULL predicate is not TRUE, so they drop out on their own. That is the behavior you want. The mistake is 'helping': `COALESCE(dur_secs, 0) BETWEEN 0 AND 2700` forces every duration-less run into the fast bucket as if it finished instantly, inflating exactly the pipelines you should be ignoring. --- ### 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. 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. #### 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 **Filtered average per pipeline** ```sql 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, polluting the averages with 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. > **Why the plan stays cheap** > > One pass over data_pipes: a range filter, a hash aggregate by a 45-value pipe_name domain, then a tiny sort. A B-tree index on dur_secs lets the engine seek the qualifying range instead of scanning all 100k rows, and the grouping stays cheap because the distinct pipeline count is small. > **Interviewers watch for** > > When someone reaches for COALESCE or IS NOT NULL here, the question to ask is 'what does a missing duration mean?' The strong answer is that it is not a completed-fast run, so it should not be in the benchmark at all, which is precisely what BETWEEN already gives you. Knowing when NOT to add a guard is the signal. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_fast_lane) - [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.