Every Touch Counts

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

junior

Asked in

Copart

Problem

Find each shopper's 2026 touchpoint total on the vehicle auction marketplace, most active first, where every row in ad_impressions and every row in search_queries from that year counts as one touchpoint. Touchpoints logged without a user_id still count, reported together on a single row of their own.

A bid, a click, a search: each one is a vote. Total them honestly.

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

search_queries

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is the UNION vs UNION ALL question wearing a marketing hat. The real skill being probed: do you know that UNION quietly deduplicates rows while UNION ALL keeps every one? You are combining two activity streams and counting events per user. If you reach for UNION, two ad impressions from the same shopper collapse into a single row before the count ever runs, so a power user with 40 touchpoints reports the same number as someone who showed up once. Your 'most engaged' ranking becomes noise, and nothing in the output looks obviously wrong.

Break down the requirements

Step 1: Reduce each source to one row per event

From ad_impressions and from search_queries, select just user_id, each filtered to 2026 on its own time column. One row out per impression and per search is exactly what 'one touchpoint' means. Do not filter out a missing user_id: the statement wants those touchpoints kept as their own row.

Step 2: Stack the streams, keep every row

UNION ALL the two selects. This is the whole problem: UNION ALL preserves duplicates so the row count equals the event count. UNION would fold identical user_id rows together and destroy the metric before you can measure it.

Step 3: Count and order

Wrap the stacked stream in a subquery, GROUP BY user_id, COUNT(*) as touchpoint_count, then sort by touchpoint_count descending. GROUP BY puts every NULL user_id into one group, which is exactly the anonymous row the statement asks for.

The solution

SELECT user_id,
       COUNT(*) AS touchpoint_count
FROM (
    SELECT user_id FROM ad_impressions
    WHERE strftime('%Y', impression_time) = '2026'
    UNION ALL
    SELECT user_id FROM search_queries
    WHERE strftime('%Y', query_time) = '2026'
) activity
GROUP BY user_id
ORDER BY touchpoint_count DESC

UNION ALL (correct here)

Appends both streams row for row. A user with 12 impressions and 8 searches contributes 20 rows, so COUNT(*) returns 20. No hidden work, no sort to dedup.

UNION (wrong here)

Runs an implicit DISTINCT across the whole stacked set. Selecting only user_id means each user collapses to one row, so every COUNT(*) returns 1 and the ranking is meaningless. It also pays for a sort or hash you never wanted.

Common follow-up questions

• Now break the total into two columns: impressions and searches side by side per user. (Pushes toward conditional aggregation, tagging each branch with a source literal before the UNION ALL, then SUM(CASE ...).)
• What changes if you only want shoppers active in BOTH channels, not either? (Shifts from UNION ALL to an INTERSECT or a join/HAVING on distinct sources, testing set-operator fluency.)
• How would you keep this incremental as new impressions and searches stream in daily? (Probes partition pruning on the time columns and whether they would pre-aggregate per day rather than rescan the full history.)