26. First-touch traffic source
Asked in screens shaped like: Airbnb, Shopify, Meta
Growth wants each customer’s first event source. First means earliest occurred_at; tie-break on event_id. Source lives in the JSON payload. Return customer_id and source, smallest customer_id first.
Requirements
- Columns: customer_id, source
- One row per customer
- source is json_extract(payload, $.source) on the first event
- Order by customer_id
Expected output
customer_id, source
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
events
~400 rows
Clickstream. payload is raw JSON — use json_extract on it.
- event_idINTEGER
- customer_idTEXT
- event_nameTEXT— page_view, add_cart, purchase, search
- occurred_atTEXT
- payloadTEXT— {"source":"web|ios","campaign":"c0…c4"}
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY occurred_at, event_id).
Hint 2
Keep rn = 1, then json_extract(payload, '$.source').
WITH first_ev AS (
SELECT customer_id, payload,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY occurred_at, event_id) AS rn
FROM events
)
SELECT customer_id, json_extract(payload, '$.source') AS source
FROM first_ev
WHERE rn = 1
ORDER BY customer_id;Why it works
First-touch is a dedup, not a GROUP BY MIN(source). MIN(source) would pick the alphabetically first channel, which is a different (wrong) question.
Run your query to see the result set.
Submit to run the checks.
Why it works
First-touch is a dedup, not a GROUP BY MIN(source). MIN(source) would pick the alphabetically first channel, which is a different (wrong) question.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems