Transaction Source Features

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

Problem

Each feature in ml_features is logged on several rows, and some of the rows whose source is 'user_sessions' were saved with no average value. Return the names of those features, each listed once, in alphabetical order.

One pipeline reviewed them. What did it see?

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 4 of 4 rows
feat_name
avg_order
cart_value
click_rate
scroll_pct

Worked solution and explanation

What this really tests

This is a test for an absent value wearing a data-quality costume. The question is which session-sourced features never got an average written down, and SQL only answers that with avg_val IS NULL. Reach for avg_val = NULL and you get back nothing, because a comparison against NULL is never true, only unknown. The second tell is quieter: every feature name sits on more than one row in ml_features, so a query that does not collapse repeats hands back each name twice.

Working through it

Step 1: Keep only the session source

Filter on source = 'user_sessions'. It earns its place: the only other rows missing an average carry no source at all, and one of them is 'user_age', which slips into the list the moment this filter goes.

Step 2: Test for a missing average, not an equal one

avg_val IS NULL keeps the rows where no average was ever recorded. avg_val = NULL asks whether an unknown equals an unknown, the answer is unknown, and a filter only passes rows that are true.

Step 3: Collapse the repeats, then sort

Every qualifying name appears on two rows here, so SELECT DISTINCT feat_name folds each to one entry. The list is read alphabetically, so ORDER BY feat_name finishes it.

The solution

Source filter plus a null test, then dedup

SELECT DISTINCT feat_name
FROM ml_features
WHERE source = 'user_sessions'
  AND avg_val IS NULL
ORDER BY feat_name

Common follow-up questions

How would you list the session features that always have an average recorded? (Per name, compare `COUNT(avg_val)` with `COUNT(*)` and keep the names where they match.)
How would you report, per feature, how many rows are missing the average? (`GROUP BY feat_name` with `COUNT(*) FILTER (WHERE avg_val IS NULL)`.)
What if the source label were stored with inconsistent casing? (Compare with `LOWER(source) = 'user_sessions'`, or normalize the label upstream.)