5. Event funnel by stage
Asked in screens shaped like: Meta, Snap, Pinterest
Product wants funnel volume per stage: how many events fired and how many distinct customers reached each one. Total events overstates reach because one customer can fire the same event repeatedly.
Requirements
- Columns: event_name, events, customers
- Only event_name in ('page_view', 'add_cart', 'purchase')
- events counts rows; customers counts distinct customer_id
- Order by customers descending, then event_name
Expected output
event_name, 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
COUNT(*) and COUNT(DISTINCT customer_id) can sit in the same SELECT.
Hint 2
Filter the three stages with IN.
Hint 3
The gap between the two counts is repeat activity per customer.
SELECT event_name,
COUNT(*) AS events,
COUNT(DISTINCT customer_id) AS customers
FROM events
WHERE event_name IN ('page_view', 'add_cart', 'purchase')
GROUP BY event_name
ORDER BY customers DESC, event_name;Why it works
This is stage volume, not a true funnel — it does not enforce that a purchase followed an add_cart for the same customer. Say that out loud in an interview; ordered funnels need window functions or self-joins on time.
Run your query to see the result set.
Submit to run the checks.
Why it works
This is stage volume, not a true funnel — it does not enforce that a purchase followed an add_cart for the same customer. Say that out loud in an interview; ordered funnels need window functions or self-joins on time.
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