# Only Here > Exclusive to one source. Missing from the other. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem The ML team is pulling the features that show up in the 'transactions' source but never in 'page_views' or 'ad_impressions', where two records describe the same feature when they share a name and a data type. Those data-type labels were recorded inconsistently, so spellings like 'INT64' and 'int64' stand for the same type and count as one. Each such feature has many transactions records, so collapse them into a single row reporting the feature name, data type, mean recorded value, and mean null percentage across those transactions records, both means rounded to two decimals. ## 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. > **The match key is `feat_name` plus a folded `dtype`** > > "Features exclusive to X that never appear in Y or Z" is a set-difference pattern. The equality condition spans two columns (`feat_name` and `dtype`), so you need a compound anti-join, and because dtype carries case-variant spellings of one logical type, the comparison must fold case. > > 1. Filter to `source = 'transactions'` > 2. Exclude any (`feat_name`, folded dtype) pair that also appears in '`page_views`' or '`ad_impressions`' > 3. Aggregate the surviving transactions rows into one row per feature --- ### 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 **Set difference with compound key exclusion** ```sql 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 ``` > **A composite index on (`feat_name`, dtype, source) keeps each probe bounded** > > The correlated subquery probes once per transactions row. With 20M total rows and roughly 2M per source, a composite index on (`feat_name`, dtype, source) turns each probe into a bounded lookup instead of a full nested-loop scan, which is the difference between a fast anti-join and a self-cross of millions of rows. > **Match on both columns, or you exclude the wrong features** > > Whether you match on BOTH `feat_name` AND dtype as the prompt specifies. Matching on name alone would incorrectly exclude features that share a name but differ in type (like the sample's `page_dwell`/string, which survives despite a `page_dwell`/int64 sitting in `page_views`), and comparing dtype without case-folding would let a differently-cased duplicate leak through (like `click_rate`, whose 'boolean' in transactions is caught by a 'BOOLEAN' in `ad_impressions`). > **`NOT IN` collapses to zero rows on a NULL sublist** > > Reaching for NOT IN against a subquery that can contain NULLs. If `feat_name` is ever NULL in the sublist, NOT IN collapses to no rows at all. NOT EXISTS has none of that three-valued-logic trap. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/only_here) - [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.