Speed and Substance

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Kabbage

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.

When a run takes longer, is it moving more rows or just stalling?

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 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.

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

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

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.)