task Segmentation & ranking · hard

Checkout review 5: prioritize channel fixes

Prioritize channels by unrecovered abandoned sessions. For each session’s first-touch channel, return sessions, abandoned_sessions, recovered_sessions, unrecovered_sessions and priority_rank.

what to return

Columns

channelsessionsabandoned_sessionsrecovered_sessionsunrecovered_sessionspriority_rank in this order

Rows

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

Abandoned means the session has checkout but no full ordered conversion. Recovered means it has a same-cart purchase in a strictly later session within 24 hours (inclusive) of any checkout in the abandoned session.

Count each abandoned session at most once in recovery, even with several purchases. unrecovered_sessions = abandoned_sessions − recovered_sessions.

Keep all first-touch channels, including zeros. Use dense descending rank of unrecovered_sessions, with ties sharing rank. Order by priority_rank, then channel.

Skill

Translate the connected review into a channel-level prioritization table. 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.