Воронка с 24-часовым окном — покупка должна следовать за просмотром в течение 24ч
Таблица events(user_id, event_type, event_time), где event_type — view или purchase. Посчитайте конверсию view→purchase, но покупка засчитывается, только если она строго после просмотра и в пределах 24 часов от него. Верните процент конверсии среди тех, кто смотрел.
-- конверсия: покупка в течение 24ч после просмотра, среди смотревшихНапишите запрос.
Соединяют покупки с просмотрами по purchase_time > view_time AND purchase_time <= view_time + interval '24 hours'. При такой паре пользователь конвертирован, поэтому считают DISTINCT конвертированных на смотревших — раз на пользователя.
- ✗Игнорировать окно и считать любого смотревшего-и-купившего
- ✗Брать рамку первый просмотр — последняя покупка, а не разрыв на пару
- ✗Брать разрыв по модулю, допуская покупку раньше просмотра
- →Как гарантировать, что каждый конвертированный посчитан один раз?
- →Как отнести покупку к самому свежему подходящему просмотру?
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.
