The Middle of the Missing

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

L4

Asked in

ESPN

Problem

The ML feature store records a null percentage for each feature, though many features were never measured. Among features whose data type is float-based, find the null percentage sitting in the middle of the recorded values.

Across the float features, how much of the data typically goes missing?

Tables

ml_features

Columns: feat_id INTEGER, feat_name TEXT, dtype TEXT, avg_val REAL, null_pct REAL, updated TEXT, source TEXT

Expected output

Worked solution and explanation

What this problem is really about

Strip away the feature-store framing and this is a plain median with two quiet traps bolted on, in an engine that has no median function. The first trap is the dtype column: the same type is logged as 'Float64', 'float64', and 'float32', so a case-sensitive match silently drops half the float features and moves the value that lands in the middle. The second trap is null_pct itself: some features were never measured, so their null_pct is NULL. Leave those rows in and the extra positions push the middle off its mark. That single oversight is the gap between the right answer and a value that is a point or two off.

The traps up close

Building the query

Step 1: Filter to measured float features

Keep rows where LOWER(dtype) LIKE '%float%' and null_pct IS NOT NULL. Both conditions carry weight: the first fixes the casing trap, the second drops the unmeasured features so they cannot distort the ordering.

Step 2: Number the values and count them

In a second CTE, attach ROW_NUMBER() OVER (ORDER BY null_pct) for position and COUNT(*) OVER () for the total. Computing both in one window pass avoids a second scan of the filtered set.

Step 3: Average the central value(s)

Keep the central rows with rn IN ((total + 1) / 2, (total + 2) / 2) and return AVG(null_pct). Integer division floors the positions, so an odd count selects one row and an even count selects the two central rows; averaging either gives the median with no interpolation function required.

WITH float_features AS (
    SELECT null_pct
    FROM ml_features
    WHERE LOWER(dtype) LIKE '%float%'
      AND null_pct IS NOT NULL
),
ranked AS (
    SELECT
        null_pct,
        ROW_NUMBER() OVER (ORDER BY null_pct) AS rn,
        COUNT(*) OVER () AS total
    FROM float_features
)
SELECT AVG(null_pct) AS median_null_pct
FROM ranked
WHERE rn IN ((total + 1) / 2, (total + 2) / 2);

`PERCENTILE_CONT`(0.5)

Reads cleanly where it exists (Postgres, DuckDB) and computes exactly the median you want, the average of the two central values on an even count. But SQLite has no such function, so the query simply fails to run here.

`ROW_NUMBER` + AVG

Portable to any engine with window functions. You own the NULL filter explicitly, and averaging the one or two central rows reproduces the standard median with no branch for odd versus even counts and no interpolation function to lean on.

Common follow-up questions

• How would you return the median null percentage separately for each source instead of one overall number? (Tests turning the overall median into a windowed one per partition.)
• If `null_pct` had heavy ties around the middle, would your answer change, and why or why not? (Checks that the candidate sees `ROW_NUMBER` breaks ties by position while the averaged value is what the answer reads.)
• At 15M rows, what would you add to keep the dtype filter and the sort fast? (Tests indexing and pre-filtering strategy on a large, low-cardinality-filtered table.)