Feature Name Intersection

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

L4

Asked in

Google

Problem

Find the feature names in ml_features that arrive from both the 'user_sessions' and 'page_views' feeds, with each feed's row count and most recent update date, listed alphabetically by feature name. The ML platform team's audit counts only profiled rows, the ones that already carry an avg_val.

Training names versus serving names. The overlap.

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

Beneath the feature-store framing this is a per-feed set intersection: which feature names show up in BOTH the user_sessions and page_views feeds. The reflex is a self-join of the two feeds, or an INTERSECT of two subqueries, but the ask also wants each feed's row count and each feed's latest date, and a self-join fans those rows out so you can no longer count them cleanly. The move is conditional aggregation: one GROUP BY feat_name pass, a COUNT(*) FILTER per feed and a MAX(CASE WHEN source ... THEN updated END) per feed, then keep only the names where both feed counts are positive. The trap bites twice here: the user_sessions feed carries unprofiled rows (avg_val IS NULL) for several of the shared features, so skipping the profiled filter inflates those tallies, and dropping the both-positive requirement returns names that live in only one feed.

Building it

Step 1: Tally each feed in one pass

Aggregate ml_features once per feat_name, but first drop the unprofiled rows with WHERE avg_val IS NOT NULL: those rows were never assigned an average value, and on this data the user_sessions feed has a handful of them for the shared features, so counting them pushes session_rows too high. Inside the single pass, use COUNT(*) FILTER (WHERE source = 'user_sessions') and the same for page_views to tally each feed's rows, and MAX(CASE WHEN source = 'user_sessions' THEN updated END) (and the page_views equivalent) to grab the newest date scoped to each feed. Rows from the other feeds, and rows whose source is null, simply fall out of every FILTER and every CASE, so no separate source filter is needed.

Step 2: Keep only the overlap

A feature is in BOTH feeds exactly when both per-feed counts are strictly positive, so keep only the rows where session_rows > 0 AND page_rows > 0. This is a null-safe, count-based restatement of the set intersection: no self-join, no INTERSECT, and it survives features that have many rows in a single feed. Finish with ORDER BY feat_name for the alphabetical output.

The solution

WITH source_presence AS (
    SELECT
        feat_name,
        COUNT(*) FILTER (WHERE source = 'user_sessions') AS session_rows,
        COUNT(*) FILTER (WHERE source = 'page_views') AS page_rows,
        MAX(CASE WHEN source = 'user_sessions' THEN updated END) AS last_seen_session,
        MAX(CASE WHEN source = 'page_views' THEN updated END) AS last_seen_page
    FROM ml_features
    WHERE avg_val IS NOT NULL
    GROUP BY feat_name
),
shared AS (
    SELECT feat_name, session_rows, page_rows, last_seen_session, last_seen_page
    FROM source_presence
    WHERE session_rows > 0 AND page_rows > 0
)
SELECT
    feat_name,
    session_rows,
    page_rows,
    last_seen_session,
    last_seen_page
FROM shared
ORDER BY feat_name;

Common follow-up questions

• Suppose the audit grows to require overlap across three named feeds instead of two. How does your query change? (Tests whether the candidate can extend the conditional-aggregation shape to N feeds without a combinatorial join.)
• The `updated` column can be null. What does `last_seen_session` return for a feed whose only rows have a null `updated` date, and is that the behavior you want? (Tests understanding of how MAX handles nulls in the `updated` column versus how the counts handle them.)
• Should a feature that appears in `page_views` but only in unprofiled `user_sessions` rows count as an intersection? How does your query decide? (Tests whether the candidate would report presence for a feed with zero qualifying rows.)
• With 5,000 distinct feature names over 20M rows, what index or partitioning would keep this aggregation from a full scan under a tight SLA? (Tests indexing intuition for a high-cardinality grouping key on a large table.)