task Cohorts & retention · hard

Checkout review 4: find later recoveries

An abandoned session contains checkout but no full ordered conversion. Find abandoned sessions with a same-cart purchase in a strictly later session within 24 hours of a checkout in the abandoned session. Return shopper_id, abandoned_session, recovery_session and recovery_minutes.

what to return

Columns

shopper_idabandoned_sessionrecovery_sessionrecovery_minutes in this order

Rows

2 rows · in the order asked

Watch for

This stage starts from the original event tables in a fresh database. Recompute any intermediate results; nothing is carried over from earlier stages.

Deduplicate globally by event_key, keeping the smallest id; a retry may have a conflicting payload.

Use (occurred_at, id) as the total event order; equal timestamps follow id order. All times are UTC.

A shopper starts a session at the first canonical event or after a gap strictly greater than 30 minutes from their previous canonical event. Exactly 30 minutes stays in the same session; midnight alone does not split it.

Session numbers start at 1 per shopper and include every event type, including heartbeat and purchase-only sessions.

A converted session has a view → checkout → purchase in that order, with all three events for the same shopper/cart within the session. Use the total event order. No extra view-to-purchase time limit applies.

A recovery needs a purchase only; the later session need not repeat the view/checkout funnel. Match shopper and cart. The 24-hour bound is inclusive.

Choose the earliest qualifying later purchase per abandoned session, then the latest qualifying checkout for that purchase. Return one row per recovered abandoned session, rounded minutes to two decimals, ordered by shopper_id,abandoned_session.

Skill

Distinguish same-session conversion from later recovery. Refresher: Sessionize an event stream →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.