# The Middle of the Missing > Across the float features, how much of the data typically goes missing? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## 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. ## 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. > **A median without MEDIAN()** > > SQLite has no MEDIAN or PERCENTILE_CONT, so build the selection yourself. Sort the recorded values with ROW_NUMBER() OVER (ORDER BY null_pct), read the row count with COUNT(*) OVER (), then keep the central rows where rn IN ((total + 1) / 2, (total + 2) / 2) and AVG them. On an odd count both expressions land on the same row, so you average one value and get it back. On an even count they land on the two central rows, so you average the pair. One clause covers odd and even with no special-casing. --- ### The traps up close > **Case-sensitive dtype match** > > WHERE dtype LIKE '%float%' keeps 'float64' and 'float32' but throws away 'Float64'. Lower the column first: LOWER(dtype) LIKE '%float%'. The data mixes 'Float64', 'float64', and 'INT64' on purpose to catch exactly this. > **Unmeasured features are not zeros** > > A feature with no recorded null_pct is not a feature with zero nulls; it is a feature you cannot place. Filter null_pct IS NOT NULL before ordering. Skip it and the extra rows change the count and shift the central position, which is precisely how a correct-looking query returns a slightly wrong value. > **One clause for odd and even** > > The rn IN ((total + 1) / 2, (total + 2) / 2) pair is the whole trick to handling odd and even counts uniformly. Because total is an integer, both divisions floor. On total = 3 both give 2, so the set is a single row. On total = 4 they give 2 and 3, so the set is the two central rows. Wrapping AVG around either case returns the standard median without a CASE branch. --- ### 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. **Median null percentage over the filtered float features** ```sql 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. > **Cost on 15M rows** > > The filter on dtype and null_pct runs first, shrinking the set the window sort has to order. The single ORDER BY null_pct sort is the dominant cost at O(n log n); computing ROW_NUMBER and COUNT in the same window pass avoids a second sort of 15M rows. > **What the interviewer is watching** > > Naming the two traps before writing SQL, casing on dtype and NULLs in null_pct, signals someone who has been burned by real data. Reaching for the ROW_NUMBER and AVG pattern instead of a nonexistent median function, and explaining how it covers even counts, shows the candidate knows the statistic and the engine's limits, not just a memorized snippet. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_middle_of_the_missing) - [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.