Funnel with a 24-hour window — a purchase must follow a view within 24h
Table events(user_id, event_type, event_time) with event_type of view or purchase. Compute the view→purchase conversion, but a purchase counts only if it happened strictly after a view and within 24 hours of it. Return the conversion percent among users who viewed.
-- conversion: purchase within 24h after a view, among viewersWrite the query.
Join each user's purchases to their views on purchase_time > view_time AND purchase_time <= view_time + interval '24 hours'. A user converts when one such pair exists, so count DISTINCT converters over distinct viewers — attributing each user once.
- ✗Ignoring the window and counting any view-plus-purchase user
- ✗Bracketing first view to last purchase instead of per-pair gaps
- ✗Using an absolute gap that lets a purchase precede its view
- →How would you make sure each converter is counted only once?
- →How would you attribute the purchase to its most recent qualifying view?
Match purchases to prior views with a directional time predicate, then attribute each user once so repeat views cannot inflate the numerator:
WITH views AS (
SELECT user_id, event_time AS view_time
FROM events WHERE event_type = 'view'
),
purchases AS (
SELECT user_id, event_time AS purchase_time
FROM events WHERE event_type = 'purchase'
),
converted AS (
SELECT DISTINCT v.user_id
FROM views v
JOIN purchases p
ON p.user_id = v.user_id
AND p.purchase_time > v.view_time
AND p.purchase_time <= v.view_time + INTERVAL '24 hours'
)
SELECT ROUND(
100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT v.user_id),
2) AS conv_within_24h_pct
FROM views v
LEFT JOIN converted c ON c.user_id = v.user_id;The predicate is directional (purchase_time > view_time) and bounded (<= view_time + 24h), so only a purchase that genuinely follows a view within the window qualifies. SELECT DISTINCT v.user_id in converted attributes each user once no matter how many qualifying view/purchase pairs they have. The LEFT JOIN back to all viewers keeps the denominator at every user who viewed.
