# Speed and Substance > When a run takes longer, is it moving more rows or just stalling? Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem We're building an observability view over pipeline runs and want a single figure for how tightly a pipeline's speed tracks its throughput. Counting only the runs that logged both a duration and their row counts, collapse each pipeline to one point: its average run duration paired with its average net row output, where a run's net output is `rows_out` minus 10 percent of `rows_in`. There's no correlation function to lean on here, so compute the Pearson correlation between those two per-pipeline figures straight from its definition. ## Worked solution and explanation ### What this problem is really testing This is a hand-rolled Pearson correlation, and the coefficient is the easy half. The half that separates candidates is spotting that not every run is measurable: two pipelines never recorded a single duration, and plenty of runs logged a duration but no row counts, or row counts but no duration. If you average duration and net output independently without first deciding which runs count, a pipeline's x average and y average get drawn from different sets of runs, and the number you hand back answers a question nobody asked. The tell that you got it wrong: the textbook computational Pearson formula and the centered formula stop agreeing with each other, because one of them quietly correlates a sample of eight pipelines against a sample of ten. > **Trick to Solving** > > Filter to runs that logged all three values (duration, rows_in, rows_out) before you collapse anything. That single WHERE guarantees each pipeline's two averages come from the same runs, drops the two pipelines that never timed a run, and makes every correct Pearson formulation land on the same answer. --- ### The trap: not every run is measurable Two pipelines in this data never logged a duration on any run, so AVG(dur_secs) for them is NULL: with no speed, they cannot be a point on a speed versus throughput plane. Many of the remaining runs are missing either a duration or a row count. Average the two figures independently and each pipeline's point is stitched from mismatched run sets; feed the duration-less pipelines into a computational Pearson and COUNT(*) reports ten while the duration sums cover only eight, so the formula divides inconsistent totals and returns garbage that can even flip sign. **Average first, filter never** AVG(dur_secs) runs over the runs that have a duration; AVG(net) runs over the runs that have row counts. Different run sets per pipeline. Two pipelines come back with a NULL duration. The computational formula counts n as ten but sums x over eight, so numerator and denominator disagree on sample size. Result: -0.19, and the centered formula lands on yet another value. **Filter to complete runs, then average** One WHERE keeps only runs with a duration and both row counts. Each pipeline's two averages share one run set, the two duration-less pipelines vanish, and n is a consistent eight everywhere. Computational and centered Pearson now agree exactly: 0.77. --- ### Building the query #### Step 1: Keep only measurable runs A run can inform speed versus throughput only if it logged its duration and both row counts, so keep the runs where dur_secs, rows_in, and rows_out are all present. This is the line the whole problem turns on. #### Step 2: Collapse each pipeline to one point GROUP BY pipe_name and take AVG(dur_secs) as x and AVG(rows_out - 0.1 * rows_in) as y. Write 0.1 as a decimal literal so the ten percent overhead is real arithmetic and not an integer 1/10 truncated to zero. #### Step 3: Broadcast the global means Take AVG(x) and AVG(y) over the per-pipeline rows in a second CTE, then CROSS JOIN it back so every pipeline row can subtract both centroids. #### Step 4: Apply Pearson by definition Numerator is SUM((x - x_mean) * (y - y_mean)). Denominator is SQRT(SUM((x - x_mean) squared)) times SQRT(SUM((y - y_mean) squared)). Divide, round to two decimals, return the single scalar named correlation. ### The solution **Manual Pearson correlation across measurable pipelines** ```sql WITH pipe_stats AS ( SELECT pipe_name, AVG(dur_secs) AS avg_dur, AVG(rows_out - 0.1 * rows_in) AS avg_net_output FROM data_pipes WHERE dur_secs IS NOT NULL AND rows_in IS NOT NULL AND rows_out IS NOT NULL GROUP BY pipe_name ), means AS ( SELECT AVG(avg_dur) AS dur_mean, AVG(avg_net_output) AS out_mean FROM pipe_stats ) SELECT ROUND( SUM((ps.avg_dur - m.dur_mean) * (ps.avg_net_output - m.out_mean)) / (SQRT(SUM((ps.avg_dur - m.dur_mean) * (ps.avg_dur - m.dur_mean))) * SQRT(SUM((ps.avg_net_output - m.out_mean) * (ps.avg_net_output - m.out_mean)))), 2 ) AS correlation FROM pipe_stats ps CROSS JOIN means m ``` > **Cost Analysis** > > The table holds a couple hundred runs across ten pipelines. The completeness filter and grouping do one pass to produce a handful of per-pipeline rows; everything after that is arithmetic over eight rows. At a thousand times the runs, the single grouped scan is still the only cost that grows. > **Common Pitfall** > > Two classic misses. Skipping the completeness filter lets the two duration-less pipelines and the partial runs poison the averages, and the computational Pearson formula then mixes sample sizes and returns a sign-flipped number. And writing the overhead as 1/10 * rows_in truncates 1/10 to zero under integer division, dropping the overhead entirely. Keep the 0.1 literal and filter first. > **Interviewers Watch For** > > Three signals. The candidate filters to complete runs before collapsing, rather than correlating whatever averaged out. They derive Pearson from first principles once they clock that no correlation function exists. And a strong one guards the denominator against a pipeline whose duration or output never varies, since zero spread zeroes the divisor. --- ## Common follow-up questions - What if a pipeline logged durations but every run has the same duration? _(Its variance is zero, the Pearson denominator hits zero, and the query errors or returns NULL. Guard with NULLIF around the denominator or exclude such pipelines.)_ - How would you keep the two duration-less pipelines in the report instead of dropping them? _(They have no speed, so they cannot enter a correlation; surface them separately as unmeasured rather than force them onto the plane.)_ - How would you switch this to a Spearman correlation instead? _(Replace each average with its rank across pipelines, then run the same Pearson formula on the ranks.)_ - Does your engine materialize the CTE or inline it? _(SQLite materializes CTEs by default. Postgres treated WITH as an optimization fence pre-12 and inlines from 12 onward. It matters once a CTE is scanned more than once.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/speed_and_substance) - [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.