18. Traffic source from JSON payload
Asked in screens shaped like: Snowflake, Databricks, Airbnb
events.payload is raw JSON with source and campaign keys. Break activity down by source without unloading the column into a staging table first — this is the semi-structured question every warehouse screen has now.
Requirements
- Columns: source, events, customers
- source is the payload’s source key
- events counts rows, customers counts distinct customer_id
- Order by events descending, then source
Expected output
source, events, customers
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
json_extract(payload, '$.source') pulls a scalar out of the JSON.
Hint 2
Group by the same extraction expression you select.
Hint 3
In Snowflake this is payload:source; in BigQuery, JSON_VALUE.
SELECT json_extract(payload, '$.source') AS source,
COUNT(*) AS events,
COUNT(DISTINCT customer_id) AS customers
FROM events
GROUP BY json_extract(payload, '$.source')
ORDER BY events DESC, source;Why it works
Extracting at query time is fine for exploration but costs a parse per row. In production you would materialise source as a typed column in the silver layer and index it.
Run your query to see the result set.
Submit to run the checks.
Why it works
Extracting at query time is fine for exploration but costs a parse per row. In production you would materialise source as a typed column in the silver layer and index it.
Pro problem
Medium 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