Drill 11 of 12 in Food delivery questions
0

Interview Question Bank · First event per user · EXISTS · inclusive time window

11. First-order cohorts and 30-day retention

HardFood deliveryQuestion #239

Concept: First event per user · EXISTS · inclusive time windowshow

Cohort retention starts from each user's first qualifying event: filter out the events that don't count, then take MIN(ts) per user. Then ask once per user, with EXISTS, whether a later qualifying event falls in the window: strictly after the first (so it can't match itself) and on or before first + INTERVAL '30 days'. Finally group users by the month of their first event.

Example (different data):

WITH f AS (SELECT member_id, MIN(checked_in_at) AS first_at FROM gym_visits GROUP BY member_id)
SELECT to_char(first_at, 'YYYY-MM') AS cohort,
  COUNT(*) AS members,
  COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM gym_visits v WHERE v.member_id = f.member_id
    AND v.checked_in_at > f.first_at AND v.checked_in_at <= f.first_at + INTERVAL '14 days')) AS came_back
FROM f
GROUP BY 1;

Your task

Brightbowl's growth team groups new customers by the month of their first delivered order and asks how many came back within 30 days.

A customer's first order is their earliest delivered order. They are retained if they have another delivered order placed after it and at most 30 days (30 × 24 hours, inclusive) later. Return cohort_month (to_char of the first order's placed_at, 'YYYY-MM'), customers, retained and retention_pct (retained as a % of customers, ROUND(..., 1)). Sort by cohort_month.

Starting Postgres in your browser…

Checking your access…