Full Funnel

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

Problem

An analyst sizing the "full lifecycle" segment says her draft list repeats the same usernames hundreds of times. Rebuild it from users, search_queries, page_views and transactions so that each user who has searched, browsed and purchased appears exactly once, alphabetically by username.

Search. Browse. Buy. Only a few do all three.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

search_queries

Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

A set intersection in a funnel costume

This is a three-set intersection dressed up as a funnel segment: who is a searcher, a browser and a buyer at once. Everyone sees that much. What separates candidates is the grain. Each activity table holds many rows per user (on this seed a buyer owns 12 to 14 transactions and up to 26 page_views), so chaining inner joins multiplies those rows and every qualifying username comes back hundreds of times. The fix is to test membership instead of matching rows, and to gate on how many different areas a user touched, never on how many rows they produced.

Step 1: Label every activity row with its area

Select just user_id plus a literal area from each source: 'search' from search_queries, 'browse' from page_views, 'purchase' from transactions. Stack them with UNION ALL. Plain UNION would dedupe the stream, but it pays for a sort over every row, and the aggregate below collapses duplicates anyway.

Step 2: Attach the stream to users

Inner-join the stacked stream to users on user_id. That carries the username the roster needs. Activity rows whose user_id is NULL fall away because NULL matches nothing, so anonymous traffic never helps anyone qualify.

Step 3: Collapse to one row per user, gate on areas

GROUP BY u.user_id, u.username turns hundreds of rows per user into one, which is the deduplication the segment needs. HAVING COUNT(DISTINCT a.area) = 3 keeps only users who touched all three areas. Grouping on user_id too keeps apart two people who share a display name.

Step 4: Sort the roster

ORDER BY u.username gives the alphabetical list. The stored ' amelia' with its leading space sorts ahead of every letter: the roster reports usernames exactly as stored.

The solution

SELECT u.username
FROM users u
JOIN (
    SELECT user_id, 'search'   AS area FROM search_queries
    UNION ALL
    SELECT user_id, 'browse'   AS area FROM page_views
    UNION ALL
    SELECT user_id, 'purchase' AS area FROM transactions
) a ON a.user_id = u.user_id
GROUP BY u.user_id, u.username
HAVING COUNT(DISTINCT a.area) = 3
ORDER BY u.username

SELECT u.username
FROM users u
JOIN search_queries s ON s.user_id = u.user_id
JOIN page_views p ON p.user_id = u.user_id
JOIN transactions t ON t.user_id = u.user_id
ORDER BY u.username

Chained inner joins

Every join multiplies the rows per user. Two searches times twenty views times thirteen purchases is 520 copies of one username. Bolting DISTINCT on top hides the copies but still builds the whole product first.

Stacked and counted

UNION ALL adds rows instead of multiplying them, so the intermediate is just the sum of the three tables. GROUP BY with COUNT(DISTINCT a.area) then asks the membership question directly.

Common follow-up questions

• How would you report how many users reached each stage? (Reuses the stacked stream, counting users per `area` instead of filtering.)
• What if a user only counts when they searched before they browsed, and browsed before they bought? (Needs each user's earliest `query_time`, `viewed_at` and `transaction_date`, compared in order.)
• Where is the biggest drop-off between stages? (Compares per-stage user counts against the previous stage.)
• What changes if `ad_impressions` becomes a fourth stage? (Only a new `UNION ALL` branch and the threshold move; the shape survives.)