Queries · q065 · Head of Merchant Success · week 1, night 4

Merchant customer base health: repeat purchase rate

“For each merchant, how much of their customer base is repeat buyers versus one-and-done, over the last 12 months?”

Manager · asked the questionSamuel WhitcombeHead of Merchant Success. New in the role, owns the relationship with the largest merchants and quarterly business reviews with them in January.

Analyst · planned the reportArjun PatelSegmenter: breaks totals down by merchant size, vertical, country and channel.

Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

Samuel Whitcombe writes to Arjun Patel
The manager, at the start of week 1
Subject: What I need for merchant QBRs this month

Hi Arjun,

I'm diving into merchant health this week ahead of January's quarterly business reviews with our biggest accounts. I know you've only got a handful of report slots a night, so here's what matters most to me, roughly in priority order:
1. Who are our biggest and most important merchants, and how are they actually doing? I want a sense of the size of each merchant's business with us (sales volume, order activity) and whether it's growing, flat, or shrinking lately. This is the backbone for every QBR conversation.
2. Which merchants look like they're at risk right now? I'm thinking about warning signs like slowing sales, rising refunds or cancellations, unpaid invoices, or a drop-off in shopper activity. I want a shortlist I can proactively reach out to before they churn.
3. Which merchants are thriving, and is there a pattern? I'd like to know who's growing fastest and whether there's anything they have in common (plan tier, vertical, region) that we could point to as a playbook for others.
4. How healthy is each merchant's own customer base? For our top merchants, I want to understand whether their shoppers are coming back and buying again, or whether they're mostly one-and-done. This tells me how defensible their business is, which matters for how I advise them.
5. Are merchants getting good support from us, and does it affect how they feel about us? I'd like a read on ticket volume and resolution experience for our top merchants, since I'll likely get asked about this directly in reviews.
For context: these go straight into prep notes for one-on-one merchant reviews, so I care most about being able to point to specific merchants by name and tell a clear story about each one's trajectory. Broad trends across all 120 merchants are useful background, but the top merchants are what I'll be sitting across the table from this month.

Let me know if you want me to rank these differently given the slot constraints — happy to cut the list if needed.

Thanks, Samuel
Arjun Patel plans the night
The analyst · week 1, night 4, request 3 of 3
Trimming detail length after earlier submission issues; same 3 reports as planned, worded more concisely to stay within limits.
Merchant customer base health: repeat purchase rate
Question
For each merchant, how much of their customer base is repeat buyers versus one-and-done, over the last 12 months?
One row per
merchant (tenant)
Columns
- merchant name
- industry
- distinct customers with at least one order in last 12 months
- repeat customers with at least two orders in last 12 months
- repeat purchase rate pct
- total orders last 12 months
- average orders per customer last 12 months

Bea writes the SQL

The engineer · 1 dry run on the empty schema, then the submission

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

SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t0.industry AS industry,
  t1.distinct_customers AS distinct_customers,
  COALESCE(t2.repeat_customers, 0) AS repeat_customers,
  ROUND(COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers, 1) AS repeat_purchase_rate_pct,
  COALESCE(t3.total_orders, 0) AS total_orders_last_12_months,
  ROUND(COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers, 2) AS avg_orders_per_customer
FROM tenants t0
INNER JOIN (
  SELECT o.tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT x.tenant_id, COUNT(*) AS repeat_customers
  FROM (
    SELECT o.tenant_id, o.user_id, COUNT(*) AS order_count
    FROM orders o
    WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
      AND o.status <> 'cancelled'
    GROUP BY o.tenant_id, o.user_id
  ) x
  WHERE x.order_count >= 2
  GROUP BY x.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id, COUNT(*) AS total_orders
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t3 ON t3.tenant_id = t0.id
ORDER BY distinct_customers DESC, repeat_purchase_rate_pct DESC, t0.id ASC
LIMIT 50

“Query compiles and returns the expected columns. Submitting now.”

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

“Submitted. The report gives one row per merchant (tenant) with name, industry, distinct customers, repeat customers, repeat purchase rate %, total orders, and average orders per customer for calendar year 2025, excluding cancelled orders, ordered by distinct customers then repeat rate then tenant_id, capped at 50 rows.”

The report comes back overnight

As Arjun Patel saw it, from the small dataset the agents worked on

tenant_id | merchant_name                 | industry                  | distinct_customers | repeat_customers | repeat_purchase_rate_pct | total_orders_last_12_months | avg_orders_per_customer
----------+-------------------------------+---------------------------+--------------------+------------------+--------------------------+-----------------------------+------------------------
        7 | Cragsley Outfitters           | Apparel & Footwear        |               1356 |              795 |                     58.6 |                        3486 |                    2.57
        2 | Marrowby & Vane               | Apparel & Footwear        |                641 |              374 |                     58.3 |                        1674 |                    2.61
       18 | Brennquell Kaffee             | Food & Coffee             |                444 |              382 |                     86.0 |                        4132 |                    9.31
       25 | Studio Vrij                   | Apparel & Footwear        |                254 |              141 |                     55.5 |                         599 |                    2.36
       40 | Petalcourt Beauty             | Beauty                    |                228 |              139 |                     61.0 |                         674 |                    2.96
       17 | Ampfeld Elektronik            | Consumer Electronics      |                209 |               94 |                     45.0 |                         432 |                    2.07
        5 | Fennel & Flint                | Home & Living             |                173 |               86 |                     49.7 |                         379 |                    2.19
       29 | Cinder Peak Sports Co.        | Sports & Outdoor          |                151 |               78 |                     51.7 |                         328 |                    2.17
       35 | Wiesgrund Wohnen              | Home & Living             |                121 |               65 |                     53.7 |                         285 |                    2.36
       42 | Gratlinie Outdoor             | Sports & Outdoor          |                114 |               52 |                     45.6 |                         226 |                    1.98
       30 | Linden & Loom                 | Home & Living             |                112 |               59 |                     52.7 |                         251 |                    2.24
        1 | Grind Collective              | Food & Coffee             |                104 |               98 |                     94.2 |                        1094 |                   10.52
       79 | Kibblewick                    | Pet Supplies              |                104 |               82 |                     78.8 |                         651 |                    6.26
       12 | Spice Route Box               | Food & Coffee             |                102 |               87 |                     85.3 |                         766 |                    7.51
        9 | Honeycomb Larder              | Food & Coffee             |                 75 |               65 |                     86.7 |                         610 |                    8.13
       11 | Ballycorran Industrial Supply | Industrial Supplies (B2B) |                 73 |               61 |                     83.6 |                         339 |                    4.64
       38 | Coastline Interiors           | Home & Living             |                 68 |               35 |                     51.5 |                         149 |                    2.19
       20 | Sourdough Club                | Food & Coffee             |                 66 |               50 |                     75.8 |                         303 |                    4.59
       10 | Wohnglück                     | Home & Living             |                 66 |               33 |                     50.0 |                         142 |                    2.15
       32 | Oakhurst Tailoring            | Apparel & Footwear        |                 63 |               39 |                     61.9 |                         155 |                    2.46
       13 | Rue Lafayette Studio          | Apparel & Footwear        |                 62 |               41 |                     66.1 |                         171 |                    2.76
       19 | Kontor24 Bürobedarf           | Office Supplies (B2B)     |                 60 |               48 |                     80.0 |                         259 |                    4.32
       26 | Velvet Ash                    | Apparel & Footwear        |                 56 |               32 |                     57.1 |                         130 |                    2.32
       58 | Nordic Bloom Cosmetics        | Beauty                    |                 53 |               34 |                     64.2 |                         135 |                    2.55
        6 | Willow & Wren                 | Home & Living             |                 53 |               22 |                     41.5 |                          95 |                    1.79
       16 | Ostwind Mode                  | Apparel & Footwear        |                 43 |               23 |                     53.5 |                         119 |                    2.77
       39 | Kayak Corner                  | Sports & Outdoor          |                 43 |               19 |                     44.2 |                         101 |                    2.35
        3 | Juniper Apothecary            | Beauty                    |                 42 |               30 |                     71.4 |                         149 |                    3.55
       82 | Thistlecombe                  | Apparel & Footwear        |                 41 |               21 |                     51.2 |                          87 |                    2.12
       48 | Ridgeback Gear                | Sports & Outdoor          |                 39 |               24 |                     61.5 |                         104 |                    2.67
        8 | Castell Fasteners             | Industrial Supplies (B2B) |                 35 |               31 |                     88.6 |                         156 |                    4.46
       14 | OfficeHub Nordic              | Office Supplies (B2B)     |                 35 |               29 |                     82.9 |                         167 |                    4.77
       41 | The Balm Room                 | Beauty                    |                 34 |               21 |                     61.8 |                         101 |                    2.97
       22 | Midland Safety Wear           | Industrial Supplies (B2B) |                 33 |               28 |                     84.8 |                         140 |                    4.24
       61 | Saffron & Sage                | Beauty                    |                 33 |               22 |                     66.7 |                         113 |                    3.42
       33 | Fell & Fjell                  | Sports & Outdoor          |                 32 |               21 |                     65.6 |                          79 |                    2.47
       74 | Sorella Skin                  | Beauty                    |                 31 |               21 |                     67.7 |                          94 |                    3.03
       46 | Feather & Fin                 | Pet Supplies              |                 30 |               28 |                     93.3 |                         215 |                    7.17
       27 | Ampere & Co.                  | Consumer Electronics      |                 30 |               18 |                     60.0 |                          60 |                     2.0
       53 | Noordzee Surfwear             | Apparel & Footwear        |                 30 |               15 |                     50.0 |                          61 |                    2.03
       28 | Atelier Nouveau               | Apparel & Footwear        |                 28 |               15 |                     53.6 |                          60 |                    2.14
       21 | Kettle & Key                  | Home & Living             |                 23 |               13 |                     56.5 |                          60 |                    2.61
       44 | Whisker Works                 | Pet Supplies              |                 22 |               16 |                     72.7 |                         177 |                    8.05
       56 | Lamplight Living              | Home & Living             |                 22 |               13 |                     59.1 |                          48 |                    2.18
       59 | Lumen Collective              | Apparel & Footwear        |                 22 |               12 |                     54.5 |                          51 |                    2.32
       36 | Fjordline Wear                | Apparel & Footwear        |                 21 |               14 |                     66.7 |                          54 |                    2.57
       52 | Hautnah                       | Beauty                    |                 20 |               15 |                     75.0 |                          91 |                    4.55
        4 | Hus & Hem                     | Home & Living             |                 20 |                5 |                     25.0 |                          34 |                     1.7
       45 | Maison Clair                  | Apparel & Footwear        |                 19 |               12 |                     63.2 |                          51 |                    2.68
       24 | Fern & Fable                  | Home & Living             |                 18 |                8 |                     44.4 |                          40 |                    2.22
(50 rows)

It becomes benchmark query q065

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

spellingfetch first
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 (5×)
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×)
placeholderfetch first

The benchmark text, against the submitted SQL

SELECT  t0.id AS tenant_id,  t0.name AS merchant_name,  t0.industry AS industry,  t1.distinct_customers AS distinct_customers,  COALESCE(t2.repeat_customers, 0) AS repeat_customers,  ROUND(COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers, 1) AS repeat_purchase_rate_pct,  COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers AS repeat_purchase_rate_pct,  COALESCE(t3.total_orders, 0) AS total_orders_last_12_months,  ROUND(COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers, 2) AS avg_orders_per_customer  COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers AS avg_orders_per_customerFROM tenants t0INNER JOIN (  SELECT o.tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers  SELECT o.tenant_id AS tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers  FROM orders o  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'  GROUP BY o.tenant_id) t1 ON t1.tenant_id = t0.idLEFT JOIN (  SELECT x.tenant_id, COUNT(*) AS repeat_customers  SELECT x.tenant_id AS tenant_id, COUNT(*) AS repeat_customers  FROM (    SELECT o.tenant_id, o.user_id, COUNT(*) AS order_count    SELECT o.tenant_id AS tenant_id, o.user_id AS user_id, COUNT(*) AS order_count    FROM orders o    WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'      AND o.status <> 'cancelled'    GROUP BY o.tenant_id, o.user_id  ) x  WHERE x.order_count >= 2  GROUP BY x.tenant_id) t2 ON t2.tenant_id = t0.idLEFT JOIN (  SELECT o.tenant_id, COUNT(*) AS total_orders  SELECT o.tenant_id AS tenant_id, COUNT(*) AS total_orders  FROM orders o  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'  GROUP BY o.tenant_id) t3 ON t3.tenant_id = t0.idORDER BY distinct_customers DESC, repeat_purchase_rate_pct DESC, t0.id ASCLIMIT 50ORDER BY distinct_customers DESC NULLS FIRST, repeat_purchase_rate_pct DESC NULLS FIRST, t0.id ASC NULLS LAST{{fetch_first(50)}}

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

SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t0.industry AS industry,
  t1.distinct_customers AS distinct_customers,
  COALESCE(t2.repeat_customers, 0) AS repeat_customers,
  COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers AS repeat_purchase_rate_pct,
  COALESCE(t3.total_orders, 0) AS total_orders_last_12_months,
  COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers AS avg_orders_per_customer
FROM tenants t0
INNER JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT x.tenant_id AS tenant_id, COUNT(*) AS repeat_customers
  FROM (
    SELECT o.tenant_id AS tenant_id, o.user_id AS user_id, COUNT(*) AS order_count
    FROM orders o
    WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
      AND o.status <> 'cancelled'
    GROUP BY o.tenant_id, o.user_id
  ) x
  WHERE x.order_count >= 2
  GROUP BY x.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS total_orders
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t3 ON t3.tenant_id = t0.id
ORDER BY distinct_customers DESC NULLS FIRST, repeat_purchase_rate_pct DESC NULLS FIRST, t0.id ASC NULLS LAST
FETCH FIRST 50 ROWS ONLY

SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t0.industry AS industry,
  t1.distinct_customers AS distinct_customers,
  COALESCE(t2.repeat_customers, 0) AS repeat_customers,
  COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers AS repeat_purchase_rate_pct,
  COALESCE(t3.total_orders, 0) AS total_orders_last_12_months,
  COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers AS avg_orders_per_customer
FROM tenants t0
INNER JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT x.tenant_id AS tenant_id, COUNT(*) AS repeat_customers
  FROM (
    SELECT o.tenant_id AS tenant_id, o.user_id AS user_id, COUNT(*) AS order_count
    FROM orders o
    WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
      AND o.status <> 'cancelled'
    GROUP BY o.tenant_id, o.user_id
  ) x
  WHERE x.order_count >= 2
  GROUP BY x.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS total_orders
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t3 ON t3.tenant_id = t0.id
ORDER BY distinct_customers DESC NULLS FIRST, repeat_purchase_rate_pct DESC NULLS FIRST, t0.id ASC NULLS LAST
LIMIT 50

Scale factor 1000, c8a.24xlarge, after the last load. q065 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

Bea's whole session, 7 turns

The request, as Bea received it

Report request from Arjun Patel (analyst for the Head of Merchant Success):

Title: Merchant customer base health: repeat purchase rate
Question: For each merchant, how much of their customer base is repeat buyers versus one-and-done, over the last 12 months?
Grain: one row per merchant (tenant)
Columns:
- merchant name
- industry
- distinct customers with at least one order in last 12 months
- repeat customers with at least two orders in last 12 months
- repeat purchase rate pct
- total orders last 12 months
- average orders per customer last 12 months
Details: Table orders: count distinct user_id per tenant with order counts over placed_at Jan 1 to Dec 31 2025, excluding cancelled orders. Join tenants for name and industry. repeat purchase rate pct equals repeat customers divided by distinct customers. average orders per customer equals total orders divided by distinct customers. Order by distinct customers descending, tie-break repeat purchase rate descending, then tenant_id. Cap 50 rows.

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

Bea calls dry_run

SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t0.industry AS industry,
  t1.distinct_customers AS distinct_customers,
  COALESCE(t2.repeat_customers, 0) AS repeat_customers,
  ROUND(COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers, 1) AS repeat_purchase_rate_pct,
  COALESCE(t3.total_orders, 0) AS total_orders_last_12_months,
  ROUND(COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers, 2) AS avg_orders_per_customer
FROM tenants t0
INNER JOIN (
  SELECT o.tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT x.tenant_id, COUNT(*) AS repeat_customers
  FROM (
    SELECT o.tenant_id, o.user_id, COUNT(*) AS order_count
    FROM orders o
    WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
      AND o.status <> 'cancelled'
    GROUP BY o.tenant_id, o.user_id
  ) x
  WHERE x.order_count >= 2
  GROUP BY x.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id, COUNT(*) AS total_orders
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t3 ON t3.tenant_id = t0.id
ORDER BY distinct_customers DESC, repeat_purchase_rate_pct DESC, t0.id ASC
LIMIT 50

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_id, merchant_name, industry, distinct_customers, repeat_customers, repeat_purchase_rate_pct, total_orders_last_12_months, avg_orders_per_customer

BeaQuery compiles and returns the expected columns. Submitting now.

Bea calls submit

SELECT
  t0.id AS tenant_id,
  t0.name AS merchant_name,
  t0.industry AS industry,
  t1.distinct_customers AS distinct_customers,
  COALESCE(t2.repeat_customers, 0) AS repeat_customers,
  ROUND(COALESCE(t2.repeat_customers, 0) * 100.0 / t1.distinct_customers, 1) AS repeat_purchase_rate_pct,
  COALESCE(t3.total_orders, 0) AS total_orders_last_12_months,
  ROUND(COALESCE(t3.total_orders, 0) * 1.0 / t1.distinct_customers, 2) AS avg_orders_per_customer
FROM tenants t0
INNER JOIN (
  SELECT o.tenant_id, COUNT(DISTINCT o.user_id) AS distinct_customers
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t1 ON t1.tenant_id = t0.id
LEFT JOIN (
  SELECT x.tenant_id, COUNT(*) AS repeat_customers
  FROM (
    SELECT o.tenant_id, o.user_id, COUNT(*) AS order_count
    FROM orders o
    WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
      AND o.status <> 'cancelled'
    GROUP BY o.tenant_id, o.user_id
  ) x
  WHERE x.order_count >= 2
  GROUP BY x.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id, COUNT(*) AS total_orders
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t3 ON t3.tenant_id = t0.id
ORDER BY distinct_customers DESC, repeat_purchase_rate_pct DESC, t0.id ASC
LIMIT 50

The warehouse answers

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

BeaSubmitted. The report gives one row per merchant (tenant) with name, industry, distinct customers, repeat customers, repeat purchase rate %, total orders, and average orders per customer for calendar year 2025, excluding cancelled orders, ordered by distinct customers then repeat rate then tenant_id, capped at 50 rows.

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

Bea's transcript (265 KB) · Arjun Patel's transcript (881 KB) · Samuel Whitcombe's transcript (261 KB) · the night's plan (3 KB) · the night's reports (34 KB) · the week's memo (3 KB)

Merchant customer base health: repeat purchase rate

Samuel Whitcombe writes to Arjun Patel

Arjun Patel plans the night

Bea writes the SQL

The report comes back overnight

It becomes benchmark query q065

Everything, as recorded