Feature Quality by Source

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

Our feature audit keeps surfacing sources that carry more high-null features (above 20 percent missing) than the typical source does, ignoring any feature with no source attached. For each of those sources in ml_features, report how many feature names are high-null, how many are clean (below 10 percent missing), and how many of its names also run clean under a different source. A name is logged many times within a source, so count each unique name once.

Quality varies by source.

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 testing

Strip the feature-store costume off and this is three different questions about one table stapled together: a per-source bucket count, a cross-source lookup, and a group-versus-average gate. The bucket count is the part everyone writes. What separates people is that two of the three answers cannot be reached with a single GROUP BY. Whether a feature name runs clean under a DIFFERENT source needs ml_features joined to itself, and 'more high-null features than the typical source' needs each source's count compared back to a number computed over every source. Miss those and you either cannot express the question at all, or you quietly drop the sources with no clean features out of the audit.

Working through it

Step 1: Pin down the two buckets

High-null is null_pct > 20 and clean is null_pct < 10, both strict, so a feature sitting exactly on 20 or 10 counts for neither. A row whose null_pct is NULL also lands in neither bucket, and you get that for free: NULL > 20 is unknown, so the CASE returns NULL and the count skips it. Drop unattributed rows up front with WHERE source IS NOT NULL so a NULL source never becomes a phantom group or skews the average later.

Step 2: Count distinct names, not rows

Each source logs most of its feature names several times, so COUNT(*) inflates every bucket. COUNT(DISTINCT CASE WHEN null_pct > 20 THEN feat_name END) does three jobs in one expression: the CASE selects only the bucket's rows, the failing branch yields NULL which COUNT ignores, and DISTINCT collapses the repeats to a real feature count. The clean side is the same idea, here computed once in clean_names with a plain DISTINCT and then a COUNT(*).

Step 3: Reach into other sources with a self-join

'Runs clean under a different source' is a cross-source lookup, so join ml_features to itself on feat_name, then keep only the pairs where h.source <> c.source and c.null_pct < 10. That <> is load-bearing: match a name to its own source and every feature looks borrowed, which is why it sits in the WHERE and the join stays a clean equi-join on feat_name. COUNT(DISTINCT h.feat_name) dedups the fan-out, since the raw table feeds both sides with repeats.

Step 4: Gate on the average, keep the empty sources

A source is in scope only if it carries more high-null features than the average source, so compare its count to (SELECT AVG(cnt) FROM (SELECT COUNT(DISTINCT CASE WHEN null_pct > 20 THEN feat_name END) AS cnt FROM attributed GROUP BY source)). Then bring the clean and borrowed counts in with LEFT JOIN and turn each missing side into a zero with CASE WHEN cc.clean_count IS NULL THEN 0 ELSE cc.clean_count END, so a source with no clean feature or no borrowed name still reports a row of zeros instead of disappearing.

The solution

WITH attributed AS (
  SELECT feat_name, null_pct, source
  FROM ml_features
  WHERE source IS NOT NULL
),
per_source AS (
  SELECT
    source,
    COUNT(DISTINCT CASE WHEN null_pct > 20 THEN feat_name END) AS high_null_count
  FROM attributed
  GROUP BY source
),
clean_names AS (
  SELECT DISTINCT source, feat_name
  FROM attributed
  WHERE null_pct < 10
),
clean_counts AS (
  SELECT source, COUNT(*) AS clean_count
  FROM clean_names
  GROUP BY source
),
borrowed AS (
  SELECT h.source, COUNT(DISTINCT h.feat_name) AS borrowed_clean_count
  FROM ml_features h
  JOIN ml_features c
    ON h.feat_name = c.feat_name
  WHERE h.source <> c.source AND c.null_pct < 10
  GROUP BY h.source
)
SELECT
  ps.source,
  ps.high_null_count,
  CASE WHEN cc.clean_count IS NULL THEN 0 ELSE cc.clean_count END AS clean_count,
  CASE WHEN b.borrowed_clean_count IS NULL THEN 0 ELSE b.borrowed_clean_count END AS borrowed_clean_count
FROM per_source ps
LEFT JOIN clean_counts cc ON ps.source = cc.source
LEFT JOIN borrowed b ON ps.source = b.source
WHERE ps.high_null_count > (
  SELECT AVG(cnt) FROM (
    SELECT COUNT(DISTINCT CASE WHEN null_pct > 20 THEN feat_name END) AS cnt
    FROM attributed
    GROUP BY source
  )
)

Common follow-up questions

Common follow-up questions

• How would you also flag a source whose high-null names are all clean somewhere else? (Tests whether the candidate can move the existence idea from a self-join to a correlated subquery and reason about which is cheaper.)
• How are features with no recorded missing rate being handled here? (Probes three-valued logic: they should explain that a NULL `null_pct` already falls into neither bucket and confirm it stays that way.)
• What happens to the average cutoff if NULL-source rows are not filtered first? (Checks whether they see that the scalar subquery must exclude unattributed rows or the cutoff shifts.)