Only Here

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Amazon

Problem

Which features in ml_features appear in the 'transactions' source but never in 'page_views' or 'ad_impressions', where two records are the same feature only when both the name and the data type match, and type spellings like 'INT64' and 'int64' count as one type? Each such feature has many transactions records, so return one row per feature with its name, its data type, and the mean recorded value and mean null percentage across those transactions records, both rounded to two decimals.

Exclusive to one source. Missing from the other.

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 really is

Strip the ML-feature language away and this is a per-(name, type) set difference: keep the transactions rows whose (feat_name, folded dtype) pair shows up in NO other source you care about, then average each survivor's transactions entries down to one line. Anyone can filter to source = 'transactions'. The two things that actually separate candidates are the compound key and the case fold. Match on feat_name alone and you wrongly drop a feature that only collides by name in another source. Compare dtype raw and an 'INT64' sails right past an 'int64' in page_views, so you keep a feature that is not actually exclusive. Both mistakes quietly corrupt the exclusion set, and the row count never tells you it happened.

Break down the requirements

Step 1: Identify transaction-source features

Filter ml_features to rows where source = 'transactions', the source whose exclusive features we want.

Step 2: Normalize the data type

Define feature equality per the prose as (feat_name AND data type). The dtype column carries case-variant spellings of the SAME logical type ('INT64' vs 'int64', 'Float64' vs 'float64'), so normalize with LOWER() so that 'same data type' is judged semantically, not by raw spelling.

Step 3: Exclude features shared with other sources

Apply an anti-join via NOT EXISTS: keep a transactions feature only when NO row with the same feat_name and same case-folded dtype exists in page_views or ad_impressions. Folding case on the comparison side is what stops a differently-cased spelling in the other sources from sneaking past the exclusivity check.

Step 4: Average each feature's transactions entries

Collapse to one row per feature over the transactions records only. Because a feature is logged many times in transactions, GROUP BY feat_name and the folded dtype, then report ROUND(AVG(avg_val), 2) and ROUND(AVG(null_pct), 2) across those transactions rows. AVG ignores NULLs, so a feature whose null_pct is entirely NULL reports NULL rather than 0. This is why the sample's two login_count entries, spelled 'int64' and 'INT64', collapse under one 'int64' label, while bounce_cnt's lone NULL null_pct rolls up to NULL.

The solution

SELECT
  f.feat_name,
  LOWER(f.dtype) AS dtype,
  ROUND(AVG(f.avg_val), 2) AS avg_val,
  ROUND(AVG(f.null_pct), 2) AS null_pct
FROM ml_features f
WHERE f.source = 'transactions'
  AND NOT EXISTS (
    SELECT 1
    FROM ml_features o
    WHERE o.feat_name = f.feat_name
      AND LOWER(o.dtype) = LOWER(f.dtype)
      AND o.source IN ('page_views', 'ad_impressions')
  )
GROUP BY f.feat_name, LOWER(f.dtype)
ORDER BY f.feat_name, dtype

Common follow-up questions

• How would you rewrite this using EXCEPT? (Tests EXCEPT syntax for set differences across a compound key, and whether the candidate remembers to normalize dtype before the set operation.)
• What if you needed features unique to each source, not just transactions? (Generalizes to GROUP BY (`feat_name`, folded dtype) with HAVING COUNT(DISTINCT source) = 1.)
• What if some `avg_val` rows are NULL and you must exclude them from the average? (AVG already ignores NULLs, so this probes whether the candidate knows the default behavior versus needing an explicit filter.)
• How would performance change with 100 sources instead of 10? (The IN list grows, but an index-backed lookup stays efficient per probe.)