Features With Missing Values

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

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Airbnb

Problem

Our model training job keeps crashing on features that never had an average value recorded. Pull every one of those records from ml_features, with the feature names in alphabetical order.

Missing data in the features.

Tables

ml_features

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

ml_features: 10 of 200 rows
feat_id	feat_name	dtype	avg_val	null_pct	updated	source
141	login_count	int64	7.3	3	2026-02-02	transactions
182	cart_value	float32	14.6	6	2026-03-03	page_views
223	page_dwell	string	21.9	9	2026-04-04	ad_impressions
264	click_rate	boolean	29.2	12	2026-05-05	NULL
305	days_active	Float64	36.5	15	2026-06-06	search_queries
346	avg_order	INT64	43.8	18	2026-07-07	orders
387	session_len	float64	51.1	21	2026-08-08	NULL
428	scroll_pct	int64	58.4	24	2026-09-09	user_sessions
469	bounce_cnt	float32	65.7	NULL	2026-01-10	transactions
510	user_age	string	73	30	2026-02-11	page_views

Expected output

First 5 of 16 rows
feat_id	feat_name	dtype	avg_val	null_pct	updated	source
1576	avg_order	int64	NULL	NULL	2026-01-09	NULL
592	cart_value	Float64	NULL	36	2026-04-13	NULL
1084	click_rate	string	NULL	72	2026-07-25	user_sessions
2068	scroll_pct	INT64	NULL	44	2026-04-21	user_sessions
2560	user_age	boolean	NULL	80	2026-07-05	NULL

Worked solution and explanation

This is a three-valued-logic check dressed up as a debugging ticket. Finding the right table is easy. What separates candidates is writing avg_val IS NULL instead of avg_val = NULL, and aiming the test at avg_val rather than the null_pct column whose name screams 'null'. Get the first one wrong and the query runs cleanly and returns zero rows. The team then concludes the feature store is fine while training keeps crashing.

The reasoning

Step 1: Pick the column that is actually missing

'Never had an average value recorded' means avg_val holds the NULL marker. null_pct is a statistic about the feature, not a hole in this record. source is also empty on 50 rows. Filtering either of those returns a different, wrong set.

Step 2: Test for the marker, not a value

NULL is not a value you can compare against. avg_val = NULL evaluates to UNKNOWN on every row, and WHERE keeps only TRUE. IS NULL is the predicate built to ask whether the marker is there, and on the seed it catches 16 of the 200 rows.

Step 3: Return the whole record, sorted by `feat_name`

The team needs the full row to trace each broken feature back to its source and dtype, so all seven columns come back. Names repeat (there are four 'avg_order' records, for example). The order only promises names A to Z, so rows that share a name can come in any order.

Records with no average value, by feature name

SELECT feat_id, feat_name, dtype, avg_val, null_pct, updated, source
FROM ml_features
WHERE avg_val IS NULL
ORDER BY feat_name

The version that looks right

SELECT feat_id, feat_name, dtype, avg_val, null_pct, updated, source
FROM ml_features
WHERE avg_val = NULL
ORDER BY feat_name

Runs without error and returns nothing: avg_val = NULL is never TRUE.

Common follow-up questions

How would you also flag records whose `null_pct` is above 50? (Combining `IS NULL` with a numeric filter, and the precedence of `OR`.)
How many records per `feat_name` are missing an average? (Moving from a row filter to a per-name count.)
How would you show 0 in place of a missing `avg_val` without hiding which rows were missing? (Defaulting a NULL while keeping a flag column.)