Median Model Accuracy

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

City and County of San Francisco

Problem

Compute the median accuracy for each model (mdl_name). When a model has an even number of records, average the two middle values. Ignore records where accuracy is null. Return results ordered alphabetically by model name.

The median accuracy. Not the mean.

Tables

ml_models

Columns: model_id INTEGER, mdl_name TEXT, version TEXT, accuracy REAL, status TEXT, train_at TEXT, framework TEXT

Expected output

Worked solution and explanation

What this really is

Strip the costume and this is a hand-rolled median in a dialect that has no median. The engine this runs on ships no PERCENTILE_CONT and no MEDIAN, so the one-line answer errors out, and AVG(accuracy) runs but hands you the arithmetic mean, which on this skewed accuracy distribution is a different number than the middle value for every model. The real skill is emulating a parity-aware median by hand: number each model's rows by accuracy, size the group, then branch on parity: one middle row when the count is odd, the average of the two middle rows when it is even. The trap is that branch. Forget to gate the even case on parity and every odd-count model silently averages two adjacent values instead of returning its true middle.

Building it

Step 1: Drop the null-accuracy rows first

Filter accuracy IS NOT NULL inside the CTE, before any windowing. Do it later and the null rows still inflate cnt and shift rn, so the middle index points at the wrong row. fraud_detect carries a null accuracy here; leave it in and both that model's count and its center move.

Step 2: Number and size each model in parallel

ROW_NUMBER() OVER (PARTITION BY mdl_name ORDER BY accuracy) ranks each row within its model, lowest accuracy first. COUNT(*) OVER (PARTITION BY mdl_name) stamps the model's total onto every one of its rows. Same partition, two windows, one pass over the filtered set.

Step 3: Branch on parity to pick the middle

This is the crux. For an even count the two central positions are cnt / 2 and cnt / 2 + 1 under integer division; for an odd count the single center is (cnt + 1) / 2. Keep the branches mutually exclusive: (cnt % 2 = 0 AND rn IN (cnt / 2, cnt / 2 + 1)) OR (cnt % 2 = 1 AND rn = (cnt + 1) / 2). The cnt % 2 = 0 guard on the first branch is load-bearing.

Step 4: Average the survivors and order the result

Whatever rows survive, AVG(accuracy) is exactly right: one surviving row averages to itself, two average to their midpoint. GROUP BY mdl_name collapses each model to a single value, and ORDER BY mdl_name produces the alphabetical output.

The solution

WITH numbered AS (
    SELECT mdl_name, accuracy,
        ROW_NUMBER() OVER (PARTITION BY mdl_name ORDER BY accuracy) AS rn,
        COUNT(*) OVER (PARTITION BY mdl_name) AS cnt
    FROM ml_models
    WHERE accuracy IS NOT NULL
)
SELECT mdl_name,
    AVG(accuracy) AS median_accuracy
FROM numbered
WHERE (cnt % 2 = 0 AND rn IN (cnt / 2, cnt / 2 + 1))
    OR (cnt % 2 = 1 AND rn = (cnt + 1) / 2)
GROUP BY mdl_name
ORDER BY mdl_name

AVG(accuracy)

Runs everywhere, but it is the arithmetic mean. On the zipf-skewed accuracy values a handful of low runs drag it below the middle value, so it disagrees with the median for every model in the fleet.

Parity-aware median

The ROW_NUMBER and COUNT(*) windows plus the even/odd branch return the actual center value, robust to the outliers that AVG chases. This is the whole reason to hand-roll it.

Common follow-up questions

• What happens if two rows in a model tie on `accuracy` at the middle positions? (Tests whether they see that `ROW_NUMBER` breaks a tie arbitrarily, yet the averaged middle value is still correct.)
• How would you adapt this to report the 90th percentile of `accuracy` instead of the median? (Tests generalizing the position arithmetic beyond the center of the distribution.)
• If `ml_models` grew to hundreds of millions of rows, what index would keep the per-model sort on `accuracy` fast? (Tests indexing the partition and order keys to keep the per-model sort cheap.)