task Active users & engagement · medium

Checkout review 1: establish the baseline

Harbor Market is investigating checkout reliability. First build a daily session baseline: session_date, sessions, distinct shoppers and returning_sessions. A returning session is any session numbered greater than 1 for that shopper in this observation dataset.

what to return

Columns

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

Assign the whole session to the UTC date of its first event, even if it crosses midnight. Order by session_date.

Skill

Establish the daily population before interpreting checkout performance. 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.