task Funnels & conversion · hard

Checkout review 2: locate funnel drop-off

Continue the checkout review by returning three rows: view, checkout and purchase. Count sessions reaching each ordered stage. Return stage_no (1–3), stage, sessions and dropoff from the preceding stage.

what to return

Columns

stage_nostagesessionsdropoff in this order

Rows

3 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.

View requires any view. Checkout requires an earlier same-cart view. Purchase requires the full ordered funnel. Count a session at most once per stage.

dropoff is previous-stage sessions minus current-stage sessions; use NULL for view. Order by stage_no.

Skill

Diagnose the funnel stage at which eligible sessions stop progressing. 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.