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

Top merchants: year-over-year holiday-quarter growth

“For the biggest merchants, is Oct-Dec revenue really growing year over year, or is the recent growth just holiday seasonality?”

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 1 of 3
Trimming detail length after earlier submission issues; same 3 reports as planned, worded more concisely to stay within limits.
Top merchants: year-over-year holiday-quarter growth
Question
For the biggest merchants, is Oct-Dec revenue really growing year over year, or is the recent growth just holiday seasonality?
One row per
merchant (tenant)
Columns
- merchant name
- industry
- revenue EUR Oct-Dec 2025
- revenue EUR Oct-Dec 2024
- revenue YoY growth pct
- orders Oct-Dec 2025
- orders Oct-Dec 2024
- order YoY growth pct

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,
  COALESCE(t1.revenue_eur_a, 0) AS revenue_eur_2025,
  COALESCE(t2.revenue_eur_b, 0) AS revenue_eur_2024,
  CASE WHEN COALESCE(t2.revenue_eur_b, 0) = 0 THEN NULL
       ELSE ROUND(((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100, 1)
  END AS revenue_yoy_growth_pct,
  COALESCE(t3.orders_a, 0) AS orders_2025,
  COALESCE(t4.orders_b, 0) AS orders_2024,
  CASE WHEN COALESCE(t4.orders_b, 0) = 0 THEN NULL
       ELSE ROUND(((COALESCE(t3.orders_a, 0) - t4.orders_b)::numeric / t4.orders_b) * 100, 1)
  END AS order_yoy_growth_pct
FROM tenants t0
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_a
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2025-10-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 o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_b
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_a
  FROM orders o
  WHERE o.placed_at >= DATE '2025-10-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
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_b
  FROM orders o
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t4 ON t4.tenant_id = t0.id
ORDER BY COALESCE(t1.revenue_eur_a, 0) DESC, t0.id ASC
LIMIT 50

“Query compiles and returns the requested columns at one-row-per-merchant grain. Submitting.”

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

“Submitted. The report returns one row per merchant (tenant) with name, industry, Q4 2025 and Q4 2024 EUR revenue (converted via daily exchange_rates), revenue YoY growth %, and order counts with YoY growth %, ordered by 2025 Q4 revenue descending, 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                  |   revenue_eur_2025 |   revenue_eur_2024 | revenue_yoy_growth_pct | orders_2025 | orders_2024 | order_yoy_growth_pct
----------+------------------------+---------------------------+--------------------+--------------------+------------------------+-------------+-------------+---------------------
        7 | Cragsley Outfitters    | Apparel & Footwear        | 305308.13477580674 |  254916.2183454923 |                   19.8 |        1385 |        1250 |                 10.8
        2 | Marrowby & Vane        | Apparel & Footwear        | 115486.55393788879 |  99879.05831601824 |                   15.6 |         632 |         536 |                 17.9
       14 | OfficeHub Nordic       | Office Supplies (B2B)     |   95374.2646994069 |  74566.95659110874 |                   27.9 |          54 |          34 |                 58.8
        8 | Castell Fasteners      | Industrial Supplies (B2B) |  89765.90754690344 |  82749.56637152552 |                    8.5 |          40 |          33 |                 21.2
       67 | Deskline Supplies      | Office Supplies (B2B)     |  49519.31307200409 |  72832.46021243864 |                  -32.0 |          23 |          21 |                  9.5
       29 | Cinder Peak Sports Co. | Sports & Outdoor          |   37423.0352890475 |  25086.90484593682 |                   49.2 |         103 |          80 |                 28.7
        5 | Fennel & Flint         | Home & Living             | 30508.548393095047 | 20993.711669953103 |                   45.3 |         172 |         114 |                 50.9
       40 | Petalcourt Beauty      | Beauty                    | 20204.690585320448 | 21367.591359327926 |                   -5.4 |         243 |         220 |                 10.5
       32 | Oakhurst Tailoring     | Apparel & Footwear        | 13578.429230015927 |  6210.799071060735 |                  118.6 |          80 |          44 |                 81.8
       26 | Velvet Ash             | Apparel & Footwear        |  11099.06167869471 |  8440.430393224973 |                   31.5 |          50 |          47 |                  6.4
       38 | Coastline Interiors    | Home & Living             |  8889.546606660853 |  7109.994602993974 |                   25.0 |          65 |          46 |                 41.3
       68 | Bytefield              | Consumer Electronics      |  8534.818988547484 |  855.2479167287883 |                  897.9 |          11 |           1 |               1000.0
        1 | Grind Collective       | Food & Coffee             |  8025.257959762262 |  7060.910689946055 |                   13.7 |         346 |         231 |                 49.8
       39 | Kayak Corner           | Sports & Outdoor          |  7541.799332190187 | 14511.595443279439 |                  -48.0 |          21 |          36 |                -41.7
       79 | Kibblewick             | Pet Supplies              |  6937.270312135312 |  5524.927006049064 |                   25.6 |         196 |         130 |                 50.8
        9 | Honeycomb Larder       | Food & Coffee             |  6774.995053655056 |  4486.233019954265 |                   51.0 |         186 |         135 |                 37.8
       82 | Thistlecombe           | Apparel & Footwear        |  6586.155813760571 |  3157.631206782606 |                  108.6 |          42 |          16 |                162.5
        3 | Juniper Apothecary     | Beauty                    |  6143.895316009281 |  4203.792296253382 |                   46.2 |          55 |          35 |                 57.1
       33 | Fell & Fjell           | Sports & Outdoor          |  5888.326844261869 |  9062.857478957485 |                  -35.0 |          17 |          22 |                -22.7
        6 | Willow & Wren          | Home & Living             | 5656.5065044825815 |  9593.534345973987 |                  -41.0 |          33 |          55 |                -40.0
       58 | Nordic Bloom Cosmetics | Beauty                    |  5088.105628107569 |  5108.549229638149 |                   -0.4 |          44 |          33 |                 33.3
       41 | The Balm Room          | Beauty                    | 3782.2293516296427 | 2633.9276053478325 |                   43.6 |          38 |          23 |                 65.2
       61 | Saffron & Sage         | Beauty                    |  3591.168799499806 |  2529.702061986444 |                   42.0 |          43 |          30 |                 43.3
       36 | Fjordline Wear         | Apparel & Footwear        | 2861.0910292157914 |  2691.802771046791 |                    6.3 |          22 |          22 |                  0.0
       47 | Tail Wag Supply        | Pet Supplies              | 2525.1108895814386 | 1393.3377865909927 |                   81.2 |          29 |          18 |                 61.1
       46 | Feather & Fin          | Pet Supplies              | 2440.8140788065944 |  1716.283811574838 |                   42.2 |          75 |          47 |                 59.6
      108 | Alpine Loft            | Sports & Outdoor          |  2430.368817325345 |                0.0 |                   NULL |           2 |           0 |                 NULL
       89 | Circuit Harbor         | Consumer Electronics      |  2360.257771442038 | 1905.8059633327136 |                   23.8 |           5 |           5 |                  0.0
       86 | Courtside Collective   | Sports & Outdoor          |  2359.677594562969 |    3318.4491605321 |                  -28.9 |           5 |           8 |                -37.5
       56 | Lamplight Living       | Home & Living             | 2343.3396257475624 |  1548.973525840177 |                   51.3 |          19 |          10 |                 90.0
       94 | Greyfriars Supply      | Apparel & Footwear        |  2275.185355913487 |                0.0 |                   NULL |           8 |           0 |                 NULL
       43 | Roast Republic         | Food & Coffee             | 1821.8625445112848 |  786.6456128756973 |                  131.6 |          21 |           8 |                162.5
       77 | Meadow Row             | Apparel & Footwear        |  1733.705254740237 | 2056.8528337600446 |                  -15.7 |           5 |           5 |                  0.0
        4 | Hus & Hem              | Home & Living             | 1720.5881410795268 | 3641.1678185491423 |                  -52.7 |          14 |          26 |                -46.2
       51 | Driftwood Home         | Home & Living             | 1551.6115265956932 | 23.481259408136467 |                 6507.9 |           9 |           1 |                800.0
      115 | Signalbox              | Consumer Electronics      | 1439.8531421743749 |                0.0 |                   NULL |           5 |           0 |                 NULL
      100 | Rosehip Lab            | Beauty                    |   1404.46589152984 |                0.0 |                   NULL |          16 |           0 |                 NULL
       49 | Stoneware Studio       | Home & Living             |  1318.165175964863 |  768.7122099522219 |                   71.5 |          12 |           3 |                300.0
       54 | Tiny Atelier           | Home & Living             | 1283.8234387644463 |  3217.663973844508 |                  -60.1 |           8 |          17 |                -52.9
       64 | Brick & Bobbin         | Apparel & Footwear        | 1224.3079174304328 | 3584.6060003355587 |                  -65.8 |          10 |          14 |                -28.6
      107 | Glasshouse Grooming    | Beauty                    |  1207.690922367792 |                0.0 |                   NULL |           8 |           0 |                 NULL
       60 | Haven Linen            | Home & Living             | 1106.5250278681465 | 2611.2191223290147 |                  -57.6 |           6 |          16 |                -62.5
       75 | Silverthread           | Jewelry & Accessories     | 1029.6122237688733 |  882.7177925455117 |                   16.6 |           6 |           3 |                100.0
       85 | Little Larch Kids      | Apparel & Footwear        | 1020.6475252913605 |  514.9604547242062 |                   98.2 |           7 |           4 |                 75.0
       62 | Folio & Fern           | Books & Stationery        | 1019.1773501235119 |  742.5124294157013 |                   37.3 |          22 |          20 |                 10.0
      114 | Copper Lane            | Apparel & Footwear        |  1003.558542492167 |                0.0 |                   NULL |           6 |           0 |                 NULL
       55 | Purrfect Pantry        | Pet Supplies              |  823.2211962547766 | 1965.1535658348416 |                  -58.1 |          17 |          22 |                -22.7
       34 | Paperplane Press       | Books & Stationery        |  684.2413219027004 |  1223.465162034375 |                  -44.1 |          15 |          18 |                -16.7
       73 | Barkley & Co.          | Pet Supplies              |   683.967507187761 |  687.1304316532863 |                   -0.5 |           8 |          12 |                -33.3
      101 | Kibble Crate           | Pet Supplies              |  415.0594406179933 |                0.0 |                   NULL |           6 |           0 |                 NULL
(50 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q062Merchant size and recent growth trend

It becomes benchmark query q063

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

spellingcast
spellingfetch first
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
made explicita selected t.col gets AS col, its standard name made explicit
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
placeholderfetch first

The benchmark text, against the submitted SQL

SELECT t0.id AS tenant_id, t0.name AS merchant_name, t0.industry,SELECT t0.id AS tenant_id, t0.name AS merchant_name, t0.industry AS industry,  COALESCE(t1.revenue_eur_a, 0) AS revenue_eur_2025,  COALESCE(t2.revenue_eur_b, 0) AS revenue_eur_2024,  CASE WHEN COALESCE(t2.revenue_eur_b, 0) = 0 THEN NULL       ELSE ROUND(((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100, 1)       ELSE ((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100  END AS revenue_yoy_growth_pct,  COALESCE(t3.orders_a, 0) AS orders_2025,  COALESCE(t4.orders_b, 0) AS orders_2024,  CASE WHEN COALESCE(t4.orders_b, 0) = 0 THEN NULL       ELSE ROUND(((COALESCE(t3.orders_a, 0) - t4.orders_b)::numeric / t4.orders_b) * 100, 1)       ELSE (CAST((COALESCE(t3.orders_a, 0) - t4.orders_b) AS numeric(18,6)) / t4.orders_b) * 100  END AS order_yoy_growth_pctFROM tenants t0LEFT JOIN (  SELECT o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_a  SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur_a  FROM orders o  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS DATE)  WHERE o.placed_at >= DATE '2025-10-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 o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_b  SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur_b  FROM orders o  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS DATE)  WHERE o.placed_at >= DATE '2024-10-01'    AND o.placed_at < DATE '2025-01-01'    AND o.status <> 'cancelled'  GROUP BY o.tenant_id) t2 ON t2.tenant_id = t0.idLEFT JOIN (  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_a  FROM orders o  WHERE o.placed_at >= DATE '2025-10-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'  GROUP BY o.tenant_id) t3 ON t3.tenant_id = t0.idLEFT JOIN (  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_b  FROM orders o  WHERE o.placed_at >= DATE '2024-10-01'    AND o.placed_at < DATE '2025-01-01'    AND o.status <> 'cancelled'  GROUP BY o.tenant_id) t4 ON t4.tenant_id = t0.idORDER BY COALESCE(t1.revenue_eur_a, 0) DESC, t0.id ASCLIMIT 50ORDER BY COALESCE(t1.revenue_eur_a, 0) 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,
  COALESCE(t1.revenue_eur_a, 0) AS revenue_eur_2025,
  COALESCE(t2.revenue_eur_b, 0) AS revenue_eur_2024,
  CASE WHEN COALESCE(t2.revenue_eur_b, 0) = 0 THEN NULL
       ELSE ((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100
  END AS revenue_yoy_growth_pct,
  COALESCE(t3.orders_a, 0) AS orders_2025,
  COALESCE(t4.orders_b, 0) AS orders_2024,
  CASE WHEN COALESCE(t4.orders_b, 0) = 0 THEN NULL
       ELSE (CAST((COALESCE(t3.orders_a, 0) - t4.orders_b) AS numeric(18,6)) / t4.orders_b) * 100
  END AS order_yoy_growth_pct
FROM tenants t0
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur_a
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2025-10-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 o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur_b
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_a
  FROM orders o
  WHERE o.placed_at >= DATE '2025-10-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
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_b
  FROM orders o
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t4 ON t4.tenant_id = t0.id
ORDER BY COALESCE(t1.revenue_eur_a, 0) 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,
  COALESCE(t1.revenue_eur_a, 0) AS revenue_eur_2025,
  COALESCE(t2.revenue_eur_b, 0) AS revenue_eur_2024,
  CASE WHEN COALESCE(t2.revenue_eur_b, 0) = 0 THEN NULL
       ELSE ((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100
  END AS revenue_yoy_growth_pct,
  COALESCE(t3.orders_a, 0) AS orders_2025,
  COALESCE(t4.orders_b, 0) AS orders_2024,
  CASE WHEN COALESCE(t4.orders_b, 0) = 0 THEN NULL
       ELSE (CAST((COALESCE(t3.orders_a, 0) - t4.orders_b) AS numeric(18,6)) / t4.orders_b) * 100
  END AS order_yoy_growth_pct
FROM tenants t0
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur_a
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2025-10-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 o.tenant_id AS tenant_id, SUM(CAST(o.grand_total / er.rate AS NUMERIC(18,6))) AS revenue_eur_b
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_a
  FROM orders o
  WHERE o.placed_at >= DATE '2025-10-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
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_b
  FROM orders o
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t4 ON t4.tenant_id = t0.id
ORDER BY COALESCE(t1.revenue_eur_a, 0) DESC NULLS FIRST, t0.id ASC NULLS LAST
LIMIT 50

Firebolt{ "errors": [ { "description": "Line 14, Column 59: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "46210b00-d61b-4888-b3b2-0c195c46e325", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table3.grand_total Decimal(14, 4) Decimal64(size = 0), __table4.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

Scale factor 1000, c8a.24xlarge, after the last load. q063 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: Top merchants: year-over-year holiday-quarter growth
Question: For the biggest merchants, is Oct-Dec revenue really growing year over year, or is the recent growth just holiday seasonality?
Grain: one row per merchant (tenant)
Columns:
- merchant name
- industry
- revenue EUR Oct-Dec 2025
- revenue EUR Oct-Dec 2024
- revenue YoY growth pct
- orders Oct-Dec 2025
- orders Oct-Dec 2024
- order YoY growth pct
Details: Tables orders, tenants, exchange_rates. Window A is placed_at Oct 1 to Dec 31 2025. Window B is placed_at Oct 1 to Dec 31 2024. Exclude cancelled orders. Convert to EUR using daily exchange_rates matched to placed_at date. growth pct equals A minus B divided by B, blank if B is zero. Order by window A revenue descending, tie-break 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,
  COALESCE(t1.revenue_eur_a, 0) AS revenue_eur_2025,
  COALESCE(t2.revenue_eur_b, 0) AS revenue_eur_2024,
  CASE WHEN COALESCE(t2.revenue_eur_b, 0) = 0 THEN NULL
       ELSE ROUND(((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100, 1)
  END AS revenue_yoy_growth_pct,
  COALESCE(t3.orders_a, 0) AS orders_2025,
  COALESCE(t4.orders_b, 0) AS orders_2024,
  CASE WHEN COALESCE(t4.orders_b, 0) = 0 THEN NULL
       ELSE ROUND(((COALESCE(t3.orders_a, 0) - t4.orders_b)::numeric / t4.orders_b) * 100, 1)
  END AS order_yoy_growth_pct
FROM tenants t0
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_a
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2025-10-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 o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_b
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_a
  FROM orders o
  WHERE o.placed_at >= DATE '2025-10-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
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_b
  FROM orders o
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t4 ON t4.tenant_id = t0.id
ORDER BY COALESCE(t1.revenue_eur_a, 0) 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, revenue_eur_2025, revenue_eur_2024, revenue_yoy_growth_pct, orders_2025, orders_2024, order_yoy_growth_pct

BeaQuery compiles and returns the requested columns at one-row-per-merchant grain. Submitting.

Bea calls submit

SELECT t0.id AS tenant_id, t0.name AS merchant_name, t0.industry,
  COALESCE(t1.revenue_eur_a, 0) AS revenue_eur_2025,
  COALESCE(t2.revenue_eur_b, 0) AS revenue_eur_2024,
  CASE WHEN COALESCE(t2.revenue_eur_b, 0) = 0 THEN NULL
       ELSE ROUND(((COALESCE(t1.revenue_eur_a, 0) - t2.revenue_eur_b) / t2.revenue_eur_b) * 100, 1)
  END AS revenue_yoy_growth_pct,
  COALESCE(t3.orders_a, 0) AS orders_2025,
  COALESCE(t4.orders_b, 0) AS orders_2024,
  CASE WHEN COALESCE(t4.orders_b, 0) = 0 THEN NULL
       ELSE ROUND(((COALESCE(t3.orders_a, 0) - t4.orders_b)::numeric / t4.orders_b) * 100, 1)
  END AS order_yoy_growth_pct
FROM tenants t0
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_a
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2025-10-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 o.tenant_id AS tenant_id, SUM(o.grand_total / er.rate) AS revenue_eur_b
  FROM orders o
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS DATE)
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t2 ON t2.tenant_id = t0.id
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_a
  FROM orders o
  WHERE o.placed_at >= DATE '2025-10-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
LEFT JOIN (
  SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_b
  FROM orders o
  WHERE o.placed_at >= DATE '2024-10-01'
    AND o.placed_at < DATE '2025-01-01'
    AND o.status <> 'cancelled'
  GROUP BY o.tenant_id
) t4 ON t4.tenant_id = t0.id
ORDER BY COALESCE(t1.revenue_eur_a, 0) DESC, t0.id ASC
LIMIT 50

The warehouse answers

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

BeaSubmitted. The report returns one row per merchant (tenant) with name, industry, Q4 2025 and Q4 2024 EUR revenue (converted via daily exchange_rates), revenue YoY growth %, and order counts with YoY growth %, ordered by 2025 Q4 revenue descending, 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 (267 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)

Top merchants: year-over-year holiday-quarter growth

Samuel Whitcombe writes to Arjun Patel

Arjun Patel plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q063

Everything, as recorded