Queries · q078 · VP Product · week 1, night 1

Repeat purchase rate and time-to-repeat by first-order cohort month

“Of shoppers who place a first order, how many come back and how long does it take?”

Manager · asked the questionJonas FeldVP Product. Joined from a mobile-first retailer. Deciding the 2026 roadmap.

Analyst · planned the reportEmeka ObiCustomer-centric: follows cohorts of shoppers or merchants over time.

Engineer · wrote the SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

A business event lands on the VP Product's desk
Week 1
We are redesigning the shopper journey for 2026. We need to understand the whole path, from the first visit to checkout, delivery, returns and the next purchase, and where shoppers drop out and why.
One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events
Jonas Feld writes to Emeka Obi
The manager, at the start of week 1
Subject: Shopper journey end-to-end — what I need for the 2026 roadmap

Emeka,

We're resetting the shopper journey for 2026, and I need a clear picture of the whole path — first visit through checkout, delivery, returns, and whether people come back — so we can decide where to invest design and engineering time first. This feeds the roadmap review I'm presenting in two weeks, so the Friday memo should leave me able to say "here's where shoppers are leaking out, and here's the one or two places a fix would move the most volume."

I know you only get a handful of report slots this week, so please prioritize in the order below — stop earlier in the list if something forces a tradeoff, rather than spreading thin across all of it.
1. Where do we lose people between landing on the storefront and placing an order? I want to see the shape of the funnel (visit → browse/search → cart → checkout → paid order) and which step is the biggest drop-off, ideally for a few of our bigger merchants so we're not averaging away real differences.
2. Once someone reaches checkout, what's stopping them from finishing? Is it a one-step cliff or a slow bleed across the checkout flow, and does it differ by device or by how they arrived (e.g. ad vs. direct vs. returning)?
3. After the order is placed, how is delivery experience affecting trust? I want to understand how often deliveries run late and whether that visibly connects to returns or to people not coming back.
4. How painful are returns, and does a return kill the relationship? I'd like a sense of how often a return leads to the shopper buying again within a reasonable window versus going quiet.
5. Who comes back, and how soon? A simple read on repeat purchase behavior — how many first-time buyers return, and roughly how long it takes — would help me know if we're building a roadmap for acquisition or for retention.
If you can only land three of these this week, do 1, 2 and 5 — funnel, checkout, and repeat purchase are the ones I most need going into the roadmap conversation. Delivery and returns can follow next week if we run out of nights.

Thanks — looking forward to Friday.

Jonas
Emeka Obi plans the night
The analyst · week 1, night 1, request 3 of 3
Jonas asked for 5 things but explicitly said: if time-constrained, prioritize #1 (funnel), #2 (checkout drop-off), #5 (repeat purchase) — delivery/returns can wait. I'm spending all 3 slots on those tonight.

Schema reality check before writing specs:
- There's no FK linking carts/web_sessions directly (carts.session_token isn't a declared FK to web_sessions.anonymous_id), so "visit" and "cart" stages have to be joined approximately — I've asked the engineer to do a best-effort match by user_id and nearest time, and to flag if that join is too noisy rather than silently forcing it.
- orders.cart_id IS a real FK, so cart→order→paid is solid.
- For repeat purchase, I'm using orders.status progression as the "reached paid" signal (no need for payments table complexity at this grain) and defining first-time buyer / repeat purchase purely off orders.placed_at per user, which is clean and well-supported by the schema.
- For device/channel on checkout, I'm leaning on web_sessions.user_agent (parsed for device class) and utm_source (channel), acknowledging this is an approximation since there's no hard session→cart link.
I'm deliberately keeping report 1 to the top ~10 merchants by volume (not all 120) per Jonas's ask to see real differences rather than an averaged-away number, and keeping report 5 to cohorts with a full 90-day observation window given data ends 2025-12-31.
Repeat purchase rate and time-to-repeat by first-order cohort month
Question
Of shoppers who place a first order, how many come back and how long does it take?
One row per
first-order cohort month (calendar month of each user's first paid order)
Columns
- cohort month
- first-time buyers
- repeat within 30 days (%)
- repeat within 60 days (%)
- repeat within 90 days (%)
- repeat buyers (any time, count)
- median days to second order (repeat buyers only)

Ines writes the SQL

The engineer · 2 dry runs on the empty schema, then the submission

Dry run 1 · STATUS: REJECTED

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at, id) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id,
    f.user_id,
    f.first_placed_at,
    s.second_placed_at,
    DATE_TRUNC('month', f.first_placed_at)::date AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN (s.second_placed_at::date - f.first_placed_at::date)
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  TO_CHAR(cohort_month, 'YYYY-MM') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*), 1) AS repeat_within_30_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*), 1) AS repeat_within_60_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*), 1) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second)) AS integer) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month

Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

WITH paid_orders AS (  SELECT tenant_id, user_id, id, placed_at,         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at, id) AS rn  FROM orders  WHERE status NOT IN ('pending','cancelled')    AND placed_at IS NOT NULL),first_orders AS (  SELECT tenant_id, user_id, placed_at AS first_placed_at  FROM paid_orders WHERE rn = 1),second_orders AS (  SELECT tenant_id, user_id, placed_at AS second_placed_at  FROM paid_orders WHERE rn = 2),joined AS (  SELECT    f.tenant_id,    f.user_id,    f.first_placed_at,    s.second_placed_at,    DATE_TRUNC('month', f.first_placed_at)::date AS cohort_month,    CASE WHEN s.second_placed_at IS NOT NULL         THEN (s.second_placed_at::date - f.first_placed_at::date)    END AS days_to_second  FROM first_orders f  LEFT JOIN second_orders s    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id)SELECT  TO_CHAR(cohort_month, 'YYYY-MM') AS cohort_month,  strftime(cohort_month, '%Y-%m') AS cohort_month,  COUNT(*) AS first_time_buyers,  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*), 1) AS repeat_within_30_pct,  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*), 1) AS repeat_within_60_pct,  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*), 1) AS repeat_within_90_pct,  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,  CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second)) AS integer) AS median_days_to_second_orderFROM joinedWHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'GROUP BY cohort_monthORDER BY cohort_month

“The query compiles and returns exactly the requested columns. Submitting.”

Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

“Submitted. Summary of the approach: - Defined "paid orders" as orders with status NOT IN ('pending','cancelled'). - Ranked each user's (tenant_id, user_id) paid orders by placed_at to identify the 1st and 2nd order per user. - Cohort month = calendar month of the first order's placed_at, restricted to 2023-01 through 2025-10. - Days-to-second-order = calendar-day difference between the two placed_at dates. - Per cohort month: counted first-time buyers, repeat buyers (any time), the % repeating within 30/60/90 days, and the median days-to-repeat among those who did repeat (nulls for non-repeaters are automatically excluded by PERCENTILE_CONT). One clarifying note included for the analyst: since user_id is only unique per tenant, "user" is treated as the (tenant_id, user_id) pair throughout.”

The report comes back overnight

As Emeka Obi saw it, from the small dataset the agents worked on

cohort_month | first_time_buyers | repeat_within_30_pct | repeat_within_60_pct | repeat_within_90_pct | repeat_buyers | median_days_to_second_order
-------------+-------------------+----------------------+----------------------+----------------------+---------------+----------------------------
2023-01      |               422 |                 27.5 |                 48.1 |                 55.7 |           349 |                          49
2023-02      |               312 |                 26.6 |                 40.1 |                 50.6 |           257 |                          65
2023-03      |               281 |                 20.3 |                 39.5 |                 52.7 |           223 |                          61
2023-04      |               265 |                 23.8 |                 34.7 |                 42.6 |           214 |                          74
2023-05      |               265 |                 24.5 |                 37.0 |                 44.2 |           209 |                          68
2023-06      |               201 |                 20.9 |                 38.3 |                 43.8 |           156 |                          62
2023-07      |               190 |                 20.5 |                 34.7 |                 50.5 |           154 |                          74
2023-08      |               214 |                 16.4 |                 31.3 |                 46.3 |           169 |                          72
2023-09      |               208 |                 20.7 |                 38.9 |                 56.3 |           160 |                          60
2023-10      |               238 |                 28.2 |                 51.7 |                 61.8 |           193 |                          44
2023-11      |               396 |                 32.6 |                 48.0 |                 54.3 |           316 |                          44
2023-12      |               340 |                 28.5 |                 43.5 |                 48.2 |           274 |                          51
2024-01      |               218 |                 19.7 |                 36.2 |                 45.4 |           171 |                          68
2024-02      |               175 |                 21.1 |                 34.9 |                 47.4 |           138 |                          67
2024-03      |               210 |                 20.0 |                 38.6 |                 44.3 |           170 |                          65
2024-04      |               194 |                 31.4 |                 42.3 |                 52.1 |           155 |                          56
2024-05      |               227 |                 19.8 |                 35.2 |                 41.4 |           171 |                          74
2024-06      |               211 |                 20.9 |                 37.4 |                 47.4 |           171 |                          66
2024-07      |               201 |                 15.9 |                 33.8 |                 43.8 |           158 |                          78
2024-08      |               204 |                 22.1 |                 35.8 |                 45.1 |           169 |                          75
2024-09      |               203 |                 22.2 |                 39.9 |                 57.6 |           164 |                          61
2024-10      |               236 |                 22.0 |                 50.8 |                 58.5 |           188 |                          48
2024-11      |               335 |                 35.8 |                 49.9 |                 53.7 |           265 |                          35
2024-12      |               457 |                 24.7 |                 40.0 |                 47.7 |           348 |                          55
2025-01      |               248 |                 20.2 |                 34.7 |                 47.2 |           183 |                          64
2025-02      |               171 |                 21.1 |                 32.7 |                 42.1 |           122 |                          67
2025-03      |               227 |                 22.5 |                 35.7 |                 44.1 |           158 |                          57
2025-04      |               195 |                 21.0 |                 33.3 |                 41.0 |           131 |                          61
2025-05      |               279 |                 19.7 |                 35.5 |                 46.2 |           192 |                          57
2025-06      |               192 |                 17.7 |                 32.3 |                 39.6 |           126 |                          61
2025-07      |               241 |                 17.8 |                 34.9 |                 42.3 |           147 |                          55
2025-08      |               226 |                 14.2 |                 30.1 |                 40.3 |           126 |                          54
2025-09      |               258 |                 20.2 |                 36.0 |                 46.9 |           135 |                          40
2025-10      |               307 |                 23.8 |                 45.6 |                 51.1 |           157 |                          32
(34 rows)

It becomes benchmark query q078

Changed only where the benchmark needs the same result on every system

spellingcast (3×)
placeholderyear month
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
made explicita selected t.col gets AS col, its standard name made explicit (4×)
placeholderpercentile cont
placeholderdate diff days
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

WITH paid_orders AS (  SELECT tenant_id, user_id, id, placed_at,         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at, id) AS rn         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at NULLS LAST, id NULLS LAST) AS rn  FROM orders  WHERE status NOT IN ('pending','cancelled')    AND placed_at IS NOT NULL),first_orders AS (  SELECT tenant_id, user_id, placed_at AS first_placed_at  FROM paid_orders WHERE rn = 1),second_orders AS (  SELECT tenant_id, user_id, placed_at AS second_placed_at  FROM paid_orders WHERE rn = 2),joined AS (  SELECT    f.tenant_id,    f.user_id,    f.first_placed_at,    s.second_placed_at,    DATE_TRUNC('month', f.first_placed_at)::date AS cohort_month,    f.tenant_id AS tenant_id,    f.user_id AS user_id,    f.first_placed_at AS first_placed_at,    s.second_placed_at AS second_placed_at,    CAST(DATE_TRUNC('month', f.first_placed_at) AS date) AS cohort_month,    CASE WHEN s.second_placed_at IS NOT NULL         THEN (s.second_placed_at::date - f.first_placed_at::date)         THEN ({{date_diff_days(f.first_placed_at, s.second_placed_at)}})    END AS days_to_second  FROM first_orders f  LEFT JOIN second_orders s    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id)SELECT  strftime(cohort_month, '%Y-%m') AS cohort_month,  {{year_month(cohort_month)}} AS cohort_month,  COUNT(*) AS first_time_buyers,  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*), 1) AS repeat_within_30_pct,  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*), 1) AS repeat_within_60_pct,  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*), 1) AS repeat_within_90_pct,  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*) AS repeat_within_30_pct,  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*) AS repeat_within_60_pct,  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*) AS repeat_within_90_pct,  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,  CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second)) AS integer) AS median_days_to_second_order  {{percentile_cont(0.5, days_to_second)}} AS median_days_to_second_orderFROM joinedWHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'GROUP BY cohort_monthORDER BY cohort_monthORDER BY cohort_month NULLS LAST

As each system received it; highlighted, the parts spelled for that system

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at NULLS LAST, id NULLS LAST) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id AS tenant_id,
    f.user_id AS user_id,
    f.first_placed_at AS first_placed_at,
    s.second_placed_at AS second_placed_at,
    CAST(DATE_TRUNC('month', f.first_placed_at) AS date) AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN (DATE_DIFF('day', f.first_placed_at, s.second_placed_at))
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  strftime(cohort_month, '%Y-%m') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*) AS repeat_within_30_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*) AS repeat_within_60_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month NULLS LAST

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at NULLS LAST, id NULLS LAST) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id AS tenant_id,
    f.user_id AS user_id,
    f.first_placed_at AS first_placed_at,
    s.second_placed_at AS second_placed_at,
    CAST(DATE_TRUNC('month', f.first_placed_at) AS date) AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN ((CAST(s.second_placed_at AS DATE) - CAST(f.first_placed_at AS DATE)))
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  to_char(cohort_month, 'YYYY-MM') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*) AS repeat_within_30_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*) AS repeat_within_60_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month NULLS LAST

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at NULLS LAST, id NULLS LAST) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id AS tenant_id,
    f.user_id AS user_id,
    f.first_placed_at AS first_placed_at,
    s.second_placed_at AS second_placed_at,
    CAST(DATE_TRUNC('month', f.first_placed_at) AS date) AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN ((CAST(s.second_placed_at AS DATE) - CAST(f.first_placed_at AS DATE)))
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  to_char(CAST(cohort_month AS timestamp), 'YYYY-MM') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*) AS repeat_within_30_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*) AS repeat_within_60_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month NULLS LAST

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at NULLS LAST, id NULLS LAST) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id AS tenant_id,
    f.user_id AS user_id,
    f.first_placed_at AS first_placed_at,
    s.second_placed_at AS second_placed_at,
    CAST(DATE_TRUNC('month', f.first_placed_at) AS date) AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN ((CAST(s.second_placed_at AS DATE) - CAST(f.first_placed_at AS DATE)))
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  to_char(cohort_month, 'YYYY-MM') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*) AS repeat_within_30_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*) AS repeat_within_60_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(days_to_second AS DOUBLE PRECISION)) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month NULLS LAST

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at NULLS LAST, id NULLS LAST) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id AS tenant_id,
    f.user_id AS user_id,
    f.first_placed_at AS first_placed_at,
    s.second_placed_at AS second_placed_at,
    CAST(DATE_TRUNC('month', f.first_placed_at) AS date) AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN (dateDiff('day', f.first_placed_at, s.second_placed_at))
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  formatDateTime(cohort_month, '%Y-%m') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*) AS repeat_within_30_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*) AS repeat_within_60_pct,
  100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  quantileExactInclusive(0.5)(toFloat64(days_to_second)) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month NULLS LAST

DataFusionrow 1: col 1: "2023-01" vs "YYYY-MM"; col 2: "437626" vs "237235"; col 3: "23.6919195843025780003930296645994525005370" vs "20.27441144856366"
ClickHouseCode: 386. DB::Exception: There is no supertype for types String, Date because some of them are String/FixedString/Enum and some of them are not: while executing 'FUNCTION greaterOrEquals(formatDateTime(CAST(dateTrunc('month'_String, __table2.first_placed_at), 'date'_String), '%Y-%m'_Str... : 2, _CA

Scale factor 1000, c8a.24xlarge, after the last load. q078 on the start page, with every configuration.

Everything, as recorded

The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

Ines's whole session, 9 turns

The request, as Ines received it

Report request from Emeka Obi (analyst for the VP Product):

Title: Repeat purchase rate and time-to-repeat by first-order cohort month
Question: Of shoppers who place a first order, how many come back and how long does it take?
Grain: one row per first-order cohort month (calendar month of each user's first paid order)
Columns:
- cohort month
- first-time buyers
- repeat within 30 days (%)
- repeat within 60 days (%)
- repeat within 90 days (%)
- repeat buyers (any time, count)
- median days to second order (repeat buyers only)
Details: Table: orders (user_id, tenant_id, placed_at, status). Define a 'paid order' as status not in ('pending','cancelled'). For each user, their first-order date is the min placed_at among their paid orders; the cohort month is that date's calendar month. A user's second order is their next paid order (by placed_at) after the first. Compute, per cohort month: count of first-time buyers; the % of those whose second order occurred within 30/60/90 days of their first; the count who ever placed a second order; and the median (days between first and second order) among those who did. Restrict to cohort months from 2023-01 through 2025-10 inclusive, so every cohort has at least a 90-day observation window against data complete through 2025-12-31. Round percentages to 1 decimal and the median to a whole number of days. Order by cohort month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Ines calls dry_run

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at, id) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id,
    f.user_id,
    f.first_placed_at,
    s.second_placed_at,
    DATE_TRUNC('month', f.first_placed_at)::date AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN (s.second_placed_at::date - f.first_placed_at::date)
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  TO_CHAR(cohort_month, 'YYYY-MM') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*), 1) AS repeat_within_30_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*), 1) AS repeat_within_60_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*), 1) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second)) AS integer) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month

The warehouse answers

STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!

Ines calls dry_run

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at, id) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id,
    f.user_id,
    f.first_placed_at,
    s.second_placed_at,
    DATE_TRUNC('month', f.first_placed_at)::date AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN (s.second_placed_at::date - f.first_placed_at::date)
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  strftime(cohort_month, '%Y-%m') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*), 1) AS repeat_within_30_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*), 1) AS repeat_within_60_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*), 1) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second)) AS integer) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: cohort_month, first_time_buyers, repeat_within_30_pct, repeat_within_60_pct, repeat_within_90_pct, repeat_buyers, median_days_to_second_order

InesThe query compiles and returns exactly the requested columns. Submitting.

Ines calls submit

WITH paid_orders AS (
  SELECT tenant_id, user_id, id, placed_at,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY placed_at, id) AS rn
  FROM orders
  WHERE status NOT IN ('pending','cancelled')
    AND placed_at IS NOT NULL
),
first_orders AS (
  SELECT tenant_id, user_id, placed_at AS first_placed_at
  FROM paid_orders WHERE rn = 1
),
second_orders AS (
  SELECT tenant_id, user_id, placed_at AS second_placed_at
  FROM paid_orders WHERE rn = 2
),
joined AS (
  SELECT
    f.tenant_id,
    f.user_id,
    f.first_placed_at,
    s.second_placed_at,
    DATE_TRUNC('month', f.first_placed_at)::date AS cohort_month,
    CASE WHEN s.second_placed_at IS NOT NULL
         THEN (s.second_placed_at::date - f.first_placed_at::date)
    END AS days_to_second
  FROM first_orders f
  LEFT JOIN second_orders s
    ON f.tenant_id = s.tenant_id AND f.user_id = s.user_id
)
SELECT
  strftime(cohort_month, '%Y-%m') AS cohort_month,
  COUNT(*) AS first_time_buyers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 30) / COUNT(*), 1) AS repeat_within_30_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 60) / COUNT(*), 1) AS repeat_within_60_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_second <= 90) / COUNT(*), 1) AS repeat_within_90_pct,
  COUNT(*) FILTER (WHERE second_placed_at IS NOT NULL) AS repeat_buyers,
  CAST(ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_second)) AS integer) AS median_days_to_second_order
FROM joined
WHERE cohort_month >= DATE '2023-01-01' AND cohort_month <= DATE '2025-10-01'
GROUP BY cohort_month
ORDER BY cohort_month

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

InesSubmitted. Summary of the approach: - Defined "paid orders" as `orders` with `status NOT IN ('pending','cancelled')`. - Ranked each user's (tenant_id, user_id) paid orders by `placed_at` to identify the 1st and 2nd order per user. - Cohort month = calendar month of the first order's `placed_at`, restricted to 2023-01 through 2025-10. - Days-to-second-order = calendar-day difference between the two `placed_at` dates. - Per cohort month: counted first-time buyers, repeat buyers (any time), the % repeating within 30/60/90 days, and the median days-to-repeat among those who did repeat (nulls for non-repeaters are automatically excluded by `PERCENTILE_CONT`). One clarifying note included for the analyst: since `user_id` is only unique per tenant, "user" is treated as the (tenant_id, user_id) pair throughout.

Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

Ines's transcript (279 KB) · Emeka Obi's transcript (864 KB) · Jonas Feld's transcript (263 KB) · the night's plan (5 KB) · the night's reports (12 KB) · the week's memo (3 KB)

Repeat purchase rate and time-to-repeat by first-order cohort month

A business event lands on the VP Product's desk

Jonas Feld writes to Emeka Obi

Emeka Obi plans the night

Ines writes the SQL

The report comes back overnight

It becomes benchmark query q078

Everything, as recorded