task Medians, shares & ratios · hard

Checkout review 3: measure the delay tail

For each first-touch channel with converted sessions, report converted_sessions, avg_seconds from checkout to purchase rounded to two decimals, and the nearest-rank p90_seconds.

what to return

Columns

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

Within a session choose the earliest qualifying purchase in total event order. For that purchase choose the latest preceding same-cart checkout that has an earlier same-cart view in the session.

Measure elapsed seconds from that checkout to purchase. Attribute to the session’s first-event channel. For n sessions, nearest-rank p90 is the sorted value at ceil(0.9*n), one-based. Include no unconverted sessions; order by channel.

Skill

Measure checkout latency without weighting sessions by repeated attempts. 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.