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

Merchant risk signals: refunds, cancellations, unpaid invoices, shopper drop-off

“Which merchants show warning signs right now: rising refunds or cancellations, unpaid invoices, or falling shopper activity?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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 2 of 3
Trimming detail length after earlier submission issues; same 3 reports as planned, worded more concisely to stay within limits.
Merchant risk signals: refunds, cancellations, unpaid invoices, shopper drop-off
Question
Which merchants show warning signs right now: rising refunds or cancellations, unpaid invoices, or falling shopper activity?
One row per
merchant (tenant)
Columns
- merchant name
- industry
- refund rate last 90 days
- refund rate prior 90 days
- cancellation rate last 90 days
- cancellation rate prior 90 days
- overdue invoice amount EUR
- web sessions last 90 days
- web sessions prior 90 days
- session growth pct

Felix 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
  t.name AS merchant_name,
  t.industry,
  round(coalesce(rr.refund_rate_last, 0) * 100, 2) AS refund_rate_last_90d_pct,
  round(coalesce(rr.refund_rate_prior, 0) * 100, 2) AS refund_rate_prior_90d_pct,
  round(coalesce(cr.cancel_rate_last, 0) * 100, 2) AS cancellation_rate_last_90d_pct,
  round(coalesce(cr.cancel_rate_prior, 0) * 100, 2) AS cancellation_rate_prior_90d_pct,
  round(coalesce(ov.overdue_eur, 0), 2) AS overdue_invoice_amount_eur,
  coalesce(ws.sessions_last, 0) AS web_sessions_last_90d,
  coalesce(ws.sessions_prior, 0) AS web_sessions_prior_90d,
  round(
    CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL
         ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior
    END, 2
  ) AS session_growth_pct
FROM tenants t
LEFT JOIN (
  SELECT ra.tenant_id,
         ra.refund_last_eur / NULLIF(ca.captured_last_eur, 0) AS refund_rate_last,
         ra.refund_prior_eur / NULLIF(ca.captured_prior_eur, 0) AS refund_rate_prior
  FROM (
    SELECT tenant_id,
           SUM(CASE WHEN processed_at >= DATE '2025-10-03' AND processed_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS refund_last_eur,
           SUM(CASE WHEN processed_at >= DATE '2025-07-05' AND processed_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS refund_prior_eur
    FROM (
      SELECT pr.tenant_id, pr.processed_at,
             pr.amount / COALESCE(er.rate, 1) AS amount_eur
      FROM payment_refunds pr
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS date)
      WHERE pr.status = 'succeeded'
        AND pr.processed_at >= DATE '2025-07-05' AND pr.processed_at < DATE '2026-01-01'
    ) ra_inner
    GROUP BY tenant_id
  ) ra
  JOIN (
    SELECT tenant_id,
           SUM(CASE WHEN captured_at >= DATE '2025-10-03' AND captured_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS captured_last_eur,
           SUM(CASE WHEN captured_at >= DATE '2025-07-05' AND captured_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS captured_prior_eur
    FROM (
      SELECT p.tenant_id, p.captured_at,
             p.amount / COALESCE(er.rate, 1) AS amount_eur
      FROM payments p
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS date)
      WHERE p.status = 'captured'
        AND p.captured_at >= DATE '2025-07-05' AND p.captured_at < DATE '2026-01-01'
    ) ca_inner
    GROUP BY tenant_id
  ) ca ON ca.tenant_id = ra.tenant_id
) rr ON rr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' THEN 1 ELSE 0 END), 0) AS cancel_rate_last,
         SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' THEN 1 ELSE 0 END), 0) AS cancel_rate_prior
  FROM orders
  WHERE placed_at >= DATE '2025-07-05' AND placed_at < DATE '2026-01-01'
  GROUP BY tenant_id
) cr ON cr.tenant_id = t.id
LEFT JOIN (
  SELECT i.tenant_id,
         SUM(i.amount_due / COALESCE(er.rate, 1)) AS overdue_eur
  FROM invoices i
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = i.currency_code
   AND er.as_of = (
        SELECT MAX(er2.as_of) FROM exchange_rates er2
        WHERE er2.base_code = 'EUR' AND er2.quote_code = i.currency_code AND er2.as_of <= DATE '2026-01-05'
      )
  WHERE i.status = 'past_due' OR (i.status = 'open' AND i.due_at < DATE '2026-01-05')
  GROUP BY i.tenant_id
) ov ON ov.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN started_at >= DATE '2025-10-03' AND started_at < DATE '2026-01-01' THEN 1 ELSE 0 END) AS sessions_last,
         SUM(CASE WHEN started_at >= DATE '2025-07-05' AND started_at < DATE '2025-10-03' THEN 1 ELSE 0 END) AS sessions_prior
  FROM web_sessions
  WHERE started_at >= DATE '2025-07-05' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) ws ON ws.tenant_id = t.id
ORDER BY overdue_invoice_amount_eur DESC, refund_rate_last_90d_pct DESC, t.id
LIMIT 50

“Query compiles and produces 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. The report joins five period-scoped subqueries (refunds, captured payments, order cancellations, overdue invoices, web sessions) back to tenants, converts all money to EUR via same-day exchange_rates, and computes the requested rates and growth percentage, ordered by overdue EUR descending.”

The report comes back overnight

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

merchant_name                 | industry                  | refund_rate_last_90d_pct | refund_rate_prior_90d_pct | cancellation_rate_last_90d_pct | cancellation_rate_prior_90d_pct | overdue_invoice_amount_eur | web_sessions_last_90d | web_sessions_prior_90d | session_growth_pct
------------------------------+---------------------------+--------------------------+---------------------------+--------------------------------+---------------------------------+----------------------------+-----------------------+------------------------+-------------------
Kontor24 Bürobedarf           | Office Supplies (B2B)     |                     0.33 |                       0.0 |                            2.6 |                            1.79 |                   64191.09 |                    58 |                     63 |              -7.94
Castell Fasteners             | Industrial Supplies (B2B) |                     0.88 |                       0.0 |                            0.0 |                             2.7 |                     5079.8 |                    40 |                     72 |             -44.44
OfficeHub Nordic              | Office Supplies (B2B)     |                      0.0 |                     26.56 |                            0.0 |                            4.65 |                    4767.95 |                    67 |                     59 |              13.56
Ballycorran Industrial Supply | Industrial Supplies (B2B) |                    11.09 |                      0.12 |                           1.14 |                            1.28 |                    3346.98 |                    80 |                     96 |             -16.67
Midland Safety Wear           | Industrial Supplies (B2B) |                      5.4 |                      9.48 |                           5.13 |                            2.08 |                    2348.21 |                    39 |                     32 |              21.88
Deskline Supplies             | Office Supplies (B2B)     |                      0.0 |                       0.0 |                            0.0 |                             0.0 |                     399.32 |                    14 |                     11 |              27.27
Polder Packaging              | Industrial Supplies (B2B) |                     13.7 |                       0.0 |                            0.0 |                             0.0 |                     169.46 |                    25 |                     13 |              92.31
Brennquell Kaffee             | Food & Coffee             |                      0.9 |                      0.91 |                           2.08 |                            0.83 |                      41.84 |                   666 |                    494 |              34.82
Brick & Bobbin                | Apparel & Footwear        |                   291.17 |                     26.86 |                            0.0 |                             0.0 |                        0.0 |                    33 |                     18 |              83.33
Kiezkind                      | Apparel & Footwear        |                   159.75 |                       0.0 |                            0.0 |                             0.0 |                        0.0 |                    25 |                     13 |              92.31
Trailmark                     | Sports & Outdoor          |                   157.98 |                     35.45 |                            0.0 |                           14.29 |                        0.0 |                    10 |                     20 |              -50.0
Bytefield                     | Consumer Electronics      |                   134.47 |                       0.0 |                           8.33 |                             0.0 |                        0.0 |                    26 |                     15 |              73.33
Little Larch Kids             | Apparel & Footwear        |                    94.74 |                     15.06 |                          22.22 |                             0.0 |                        0.0 |                    19 |                      8 |              137.5
Fell & Fjell                  | Sports & Outdoor          |                    89.11 |                      4.47 |                           5.56 |                            9.09 |                        0.0 |                    53 |                     60 |             -11.67
Ondine Botanicals             | Beauty                    |                    61.07 |                      5.23 |                            0.0 |                           14.29 |                        0.0 |                    23 |                     11 |             109.09
Northstar Watches             | Jewelry & Accessories     |                    53.24 |                       0.0 |                           20.0 |                             0.0 |                        0.0 |                    30 |                     18 |              66.67
Fjordline Wear                | Apparel & Footwear        |                    42.26 |                     54.72 |                           12.0 |                             0.0 |                        0.0 |                    68 |                     27 |             151.85
Lumen Collective              | Apparel & Footwear        |                    40.79 |                     35.36 |                           4.55 |                             0.0 |                        0.0 |                    36 |                     27 |              33.33
Cragsley Outfitters           | Apparel & Footwear        |                    38.34 |                     44.45 |                           3.93 |                            4.59 |                        0.0 |                  2942 |                   1858 |              58.34
Studio Vrij                   | Apparel & Footwear        |                    37.28 |                     88.42 |                           5.93 |                            5.66 |                        0.0 |                   423 |                    310 |              36.45
Atelier Nouveau               | Apparel & Footwear        |                    36.65 |                     40.59 |                           3.13 |                            5.88 |                        0.0 |                    73 |                     23 |             217.39
Marrowby & Vane               | Apparel & Footwear        |                    31.77 |                     39.47 |                           4.57 |                            5.08 |                        0.0 |                  1299 |                    826 |              57.26
Lamplight Living              | Home & Living             |                    30.88 |                      0.73 |                           5.26 |                             0.0 |                        0.0 |                    69 |                     35 |              97.14
Copper Lane                   | Apparel & Footwear        |                    27.79 |                       0.0 |                            0.0 |                             0.0 |                        0.0 |                    25 |                      2 |             1150.0
Gratlinie Outdoor             | Sports & Outdoor          |                    25.09 |                     22.79 |                           1.37 |                            5.66 |                        0.0 |                   266 |                    169 |               57.4
Cinder Peak Sports Co.        | Sports & Outdoor          |                     24.1 |                     10.31 |                           2.83 |                            5.56 |                        0.0 |                   264 |                    206 |              28.16
Rue Lafayette Studio          | Apparel & Footwear        |                    23.89 |                     31.33 |                           6.35 |                           13.95 |                        0.0 |                   136 |                     78 |              74.36
Velvet Ash                    | Apparel & Footwear        |                    23.54 |                     53.65 |                           3.85 |                            3.57 |                        0.0 |                    78 |                     62 |              25.81
Bijoux Lune                   | Jewelry & Accessories     |                    23.23 |                       0.0 |                            0.0 |                             0.0 |                        0.0 |                    18 |                     18 |                0.0
Ostwind Mode                  | Apparel & Footwear        |                    21.01 |                     21.53 |                            0.0 |                            4.35 |                        0.0 |                    69 |                     65 |               6.15
Thistlecombe                  | Apparel & Footwear        |                    20.79 |                     29.15 |                           4.55 |                            8.33 |                        0.0 |                   133 |                     63 |             111.11
Oakhurst Tailoring            | Apparel & Footwear        |                    20.52 |                     15.83 |                           3.61 |                           10.81 |                        0.0 |                   131 |                     81 |              61.73
Willow & Wren                 | Home & Living             |                    20.41 |                      7.71 |                           3.03 |                             0.0 |                        0.0 |                    91 |                     60 |              51.67
Velours Paris                 | Beauty                    |                    20.19 |                       0.0 |                            0.0 |                             0.0 |                        0.0 |                    21 |                     11 |              90.91
Strandgut                     | Apparel & Footwear        |                    16.08 |                     74.04 |                            0.0 |                             0.0 |                        0.0 |                    41 |                     21 |              95.24
Lumio Tech                    | Consumer Electronics      |                    14.74 |                       0.0 |                            0.0 |                             0.0 |                        0.0 |                    23 |                      5 |              360.0
Sorella Skin                  | Beauty                    |                    14.62 |                      2.41 |                           6.67 |                            4.17 |                        0.0 |                    43 |                     23 |              86.96
Ampfeld Elektronik            | Consumer Electronics      |                     14.5 |                      2.52 |                           4.17 |                             3.8 |                        0.0 |                   683 |                    292 |              133.9
Ridgeback Gear                | Sports & Outdoor          |                    14.14 |                     17.35 |                           3.33 |                            7.69 |                        0.0 |                    44 |                     39 |              12.82
Hus & Hem                     | Home & Living             |                    13.87 |                     12.11 |                           7.14 |                           22.22 |                        0.0 |                    71 |                     26 |             173.08
Saffron & Sage                | Beauty                    |                    13.33 |                     10.65 |                           2.27 |                             8.0 |                        0.0 |                    47 |                     31 |              51.61
Noordzee Surfwear             | Apparel & Footwear        |                    11.72 |                     26.41 |                            0.0 |                            12.5 |                        0.0 |                    67 |                     42 |              59.52
Kettle & Key                  | Home & Living             |                    11.71 |                       0.0 |                            0.0 |                             0.0 |                        0.0 |                    47 |                     31 |              51.61
Seilschaft                    | Sports & Outdoor          |                    11.51 |                       0.0 |                          22.22 |                             0.0 |                        0.0 |                    35 |                     10 |              250.0
Maison Clair                  | Apparel & Footwear        |                    11.13 |                     12.44 |                           7.41 |                             0.0 |                        0.0 |                    44 |                     21 |             109.52
Nordic Bloom Cosmetics        | Beauty                    |                    10.59 |                     15.03 |                          10.42 |                           10.81 |                        0.0 |                    74 |                     62 |              19.35
Basecamp Nord                 | Sports & Outdoor          |                    10.29 |                      5.57 |                           10.0 |                             0.0 |                        0.0 |                    24 |                     13 |              84.62
Wiesgrund Wohnen              | Home & Living             |                    10.13 |                     11.57 |                            2.7 |                            3.28 |                        0.0 |                   223 |                    159 |              40.25
Feather & Fin                 | Pet Supplies              |                     9.93 |                      4.61 |                           3.95 |                             2.0 |                        0.0 |                    58 |                     47 |               23.4
Petalcourt Beauty             | Beauty                    |                     9.38 |                     13.98 |                           5.16 |                            4.32 |                        0.0 |                   481 |                    216 |             122.69
(50 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q268Castell Fasteners traffic drop: seasonal pattern or anomaly across B2B wholesale merchants
It builds on:
- q062Merchant size and recent growth trend

It becomes benchmark query q064

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

spellingcast (2×)
spellingfetch first
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (3×)
made explicita selected t.col gets AS col, its standard name made explicit (7×)
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  t.name AS merchant_name,  t.industry,  round(coalesce(rr.refund_rate_last, 0) * 100, 2) AS refund_rate_last_90d_pct,  round(coalesce(rr.refund_rate_prior, 0) * 100, 2) AS refund_rate_prior_90d_pct,  round(coalesce(cr.cancel_rate_last, 0) * 100, 2) AS cancellation_rate_last_90d_pct,  round(coalesce(cr.cancel_rate_prior, 0) * 100, 2) AS cancellation_rate_prior_90d_pct,  round(coalesce(ov.overdue_eur, 0), 2) AS overdue_invoice_amount_eur,  t.industry AS industry,  coalesce(rr.refund_rate_last, 0) * 100 AS refund_rate_last_90d_pct,  coalesce(rr.refund_rate_prior, 0) * 100 AS refund_rate_prior_90d_pct,  coalesce(cr.cancel_rate_last, 0) * 100 AS cancellation_rate_last_90d_pct,  coalesce(cr.cancel_rate_prior, 0) * 100 AS cancellation_rate_prior_90d_pct,  coalesce(ov.overdue_eur, 0) AS overdue_invoice_amount_eur,  coalesce(ws.sessions_last, 0) AS web_sessions_last_90d,  coalesce(ws.sessions_prior, 0) AS web_sessions_prior_90d,  round(    CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL         ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior    END, 2  ) AS session_growth_pct  CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL       ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior  END AS session_growth_pctFROM tenants tLEFT JOIN (  SELECT ra.tenant_id,  SELECT ra.tenant_id AS tenant_id,         ra.refund_last_eur / NULLIF(ca.captured_last_eur, 0) AS refund_rate_last,         ra.refund_prior_eur / NULLIF(ca.captured_prior_eur, 0) AS refund_rate_prior  FROM (    SELECT tenant_id,           SUM(CASE WHEN processed_at >= DATE '2025-10-03' AND processed_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS refund_last_eur,           SUM(CASE WHEN processed_at >= DATE '2025-07-05' AND processed_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS refund_prior_eur    FROM (      SELECT pr.tenant_id, pr.processed_at,             pr.amount / COALESCE(er.rate, 1) AS amount_eur      SELECT pr.tenant_id AS tenant_id, pr.processed_at AS processed_at,             CAST(pr.amount / COALESCE(er.rate, 1) AS NUMERIC(18,6)) AS amount_eur      FROM payment_refunds pr      LEFT JOIN exchange_rates er        ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS date)      WHERE pr.status = 'succeeded'        AND pr.processed_at >= DATE '2025-07-05' AND pr.processed_at < DATE '2026-01-01'    ) ra_inner    GROUP BY tenant_id  ) ra  JOIN (    SELECT tenant_id,           SUM(CASE WHEN captured_at >= DATE '2025-10-03' AND captured_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS captured_last_eur,           SUM(CASE WHEN captured_at >= DATE '2025-07-05' AND captured_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS captured_prior_eur    FROM (      SELECT p.tenant_id, p.captured_at,             p.amount / COALESCE(er.rate, 1) AS amount_eur      SELECT p.tenant_id AS tenant_id, p.captured_at AS captured_at,             CAST(p.amount / COALESCE(er.rate, 1) AS NUMERIC(18,6)) AS amount_eur      FROM payments p      LEFT JOIN exchange_rates er        ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS date)      WHERE p.status = 'captured'        AND p.captured_at >= DATE '2025-07-05' AND p.captured_at < DATE '2026-01-01'    ) ca_inner    GROUP BY tenant_id  ) ca ON ca.tenant_id = ra.tenant_id) rr ON rr.tenant_id = t.idLEFT JOIN (  SELECT tenant_id,         SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal         CAST(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END) AS decimal(18,6))           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' THEN 1 ELSE 0 END), 0) AS cancel_rate_last,         SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal         CAST(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END) AS decimal(18,6))           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' THEN 1 ELSE 0 END), 0) AS cancel_rate_prior  FROM orders  WHERE placed_at >= DATE '2025-07-05' AND placed_at < DATE '2026-01-01'  GROUP BY tenant_id) cr ON cr.tenant_id = t.idLEFT JOIN (  SELECT i.tenant_id,         SUM(i.amount_due / COALESCE(er.rate, 1)) AS overdue_eur  SELECT i.tenant_id AS tenant_id,         SUM(CAST(i.amount_due / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS overdue_eur  FROM invoices i  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = i.currency_code   AND er.as_of = (        SELECT MAX(er2.as_of) FROM exchange_rates er2        WHERE er2.base_code = 'EUR' AND er2.quote_code = i.currency_code AND er2.as_of <= DATE '2026-01-05'      )  WHERE i.status = 'past_due' OR (i.status = 'open' AND i.due_at < DATE '2026-01-05')  GROUP BY i.tenant_id) ov ON ov.tenant_id = t.idLEFT JOIN (  SELECT tenant_id,         SUM(CASE WHEN started_at >= DATE '2025-10-03' AND started_at < DATE '2026-01-01' THEN 1 ELSE 0 END) AS sessions_last,         SUM(CASE WHEN started_at >= DATE '2025-07-05' AND started_at < DATE '2025-10-03' THEN 1 ELSE 0 END) AS sessions_prior  FROM web_sessions  WHERE started_at >= DATE '2025-07-05' AND started_at < DATE '2026-01-01'  GROUP BY tenant_id) ws ON ws.tenant_id = t.idORDER BY overdue_invoice_amount_eur DESC, refund_rate_last_90d_pct DESC, t.idLIMIT 50ORDER BY overdue_invoice_amount_eur DESC NULLS FIRST, refund_rate_last_90d_pct DESC NULLS FIRST, t.id NULLS LAST{{fetch_first(50)}}

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

SELECT
  t.name AS merchant_name,
  t.industry AS industry,
  coalesce(rr.refund_rate_last, 0) * 100 AS refund_rate_last_90d_pct,
  coalesce(rr.refund_rate_prior, 0) * 100 AS refund_rate_prior_90d_pct,
  coalesce(cr.cancel_rate_last, 0) * 100 AS cancellation_rate_last_90d_pct,
  coalesce(cr.cancel_rate_prior, 0) * 100 AS cancellation_rate_prior_90d_pct,
  coalesce(ov.overdue_eur, 0) AS overdue_invoice_amount_eur,
  coalesce(ws.sessions_last, 0) AS web_sessions_last_90d,
  coalesce(ws.sessions_prior, 0) AS web_sessions_prior_90d,
  CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL
       ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior
  END AS session_growth_pct
FROM tenants t
LEFT JOIN (
  SELECT ra.tenant_id AS tenant_id,
         ra.refund_last_eur / NULLIF(ca.captured_last_eur, 0) AS refund_rate_last,
         ra.refund_prior_eur / NULLIF(ca.captured_prior_eur, 0) AS refund_rate_prior
  FROM (
    SELECT tenant_id,
           SUM(CASE WHEN processed_at >= DATE '2025-10-03' AND processed_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS refund_last_eur,
           SUM(CASE WHEN processed_at >= DATE '2025-07-05' AND processed_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS refund_prior_eur
    FROM (
      SELECT pr.tenant_id AS tenant_id, pr.processed_at AS processed_at,
             CAST(pr.amount / COALESCE(er.rate, 1) AS NUMERIC(18,6)) AS amount_eur
      FROM payment_refunds pr
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS date)
      WHERE pr.status = 'succeeded'
        AND pr.processed_at >= DATE '2025-07-05' AND pr.processed_at < DATE '2026-01-01'
    ) ra_inner
    GROUP BY tenant_id
  ) ra
  JOIN (
    SELECT tenant_id,
           SUM(CASE WHEN captured_at >= DATE '2025-10-03' AND captured_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS captured_last_eur,
           SUM(CASE WHEN captured_at >= DATE '2025-07-05' AND captured_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS captured_prior_eur
    FROM (
      SELECT p.tenant_id AS tenant_id, p.captured_at AS captured_at,
             CAST(p.amount / COALESCE(er.rate, 1) AS NUMERIC(18,6)) AS amount_eur
      FROM payments p
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS date)
      WHERE p.status = 'captured'
        AND p.captured_at >= DATE '2025-07-05' AND p.captured_at < DATE '2026-01-01'
    ) ca_inner
    GROUP BY tenant_id
  ) ca ON ca.tenant_id = ra.tenant_id
) rr ON rr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         CAST(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END) AS decimal(18,6))
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' THEN 1 ELSE 0 END), 0) AS cancel_rate_last,
         CAST(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END) AS decimal(18,6))
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' THEN 1 ELSE 0 END), 0) AS cancel_rate_prior
  FROM orders
  WHERE placed_at >= DATE '2025-07-05' AND placed_at < DATE '2026-01-01'
  GROUP BY tenant_id
) cr ON cr.tenant_id = t.id
LEFT JOIN (
  SELECT i.tenant_id AS tenant_id,
         SUM(CAST(i.amount_due / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS overdue_eur
  FROM invoices i
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = i.currency_code
   AND er.as_of = (
        SELECT MAX(er2.as_of) FROM exchange_rates er2
        WHERE er2.base_code = 'EUR' AND er2.quote_code = i.currency_code AND er2.as_of <= DATE '2026-01-05'
      )
  WHERE i.status = 'past_due' OR (i.status = 'open' AND i.due_at < DATE '2026-01-05')
  GROUP BY i.tenant_id
) ov ON ov.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN started_at >= DATE '2025-10-03' AND started_at < DATE '2026-01-01' THEN 1 ELSE 0 END) AS sessions_last,
         SUM(CASE WHEN started_at >= DATE '2025-07-05' AND started_at < DATE '2025-10-03' THEN 1 ELSE 0 END) AS sessions_prior
  FROM web_sessions
  WHERE started_at >= DATE '2025-07-05' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) ws ON ws.tenant_id = t.id
ORDER BY overdue_invoice_amount_eur DESC NULLS FIRST, refund_rate_last_90d_pct DESC NULLS FIRST, t.id NULLS LAST
FETCH FIRST 50 ROWS ONLY

SELECT
  t.name AS merchant_name,
  t.industry AS industry,
  coalesce(rr.refund_rate_last, 0) * 100 AS refund_rate_last_90d_pct,
  coalesce(rr.refund_rate_prior, 0) * 100 AS refund_rate_prior_90d_pct,
  coalesce(cr.cancel_rate_last, 0) * 100 AS cancellation_rate_last_90d_pct,
  coalesce(cr.cancel_rate_prior, 0) * 100 AS cancellation_rate_prior_90d_pct,
  coalesce(ov.overdue_eur, 0) AS overdue_invoice_amount_eur,
  coalesce(ws.sessions_last, 0) AS web_sessions_last_90d,
  coalesce(ws.sessions_prior, 0) AS web_sessions_prior_90d,
  CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL
       ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior
  END AS session_growth_pct
FROM tenants t
LEFT JOIN (
  SELECT ra.tenant_id AS tenant_id,
         ra.refund_last_eur / NULLIF(ca.captured_last_eur, 0) AS refund_rate_last,
         ra.refund_prior_eur / NULLIF(ca.captured_prior_eur, 0) AS refund_rate_prior
  FROM (
    SELECT tenant_id,
           SUM(CASE WHEN processed_at >= DATE '2025-10-03' AND processed_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS refund_last_eur,
           SUM(CASE WHEN processed_at >= DATE '2025-07-05' AND processed_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS refund_prior_eur
    FROM (
      SELECT pr.tenant_id AS tenant_id, pr.processed_at AS processed_at,
             CAST(pr.amount / COALESCE(er.rate, 1) AS NUMERIC(18,6)) AS amount_eur
      FROM payment_refunds pr
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS date)
      WHERE pr.status = 'succeeded'
        AND pr.processed_at >= DATE '2025-07-05' AND pr.processed_at < DATE '2026-01-01'
    ) ra_inner
    GROUP BY tenant_id
  ) ra
  JOIN (
    SELECT tenant_id,
           SUM(CASE WHEN captured_at >= DATE '2025-10-03' AND captured_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS captured_last_eur,
           SUM(CASE WHEN captured_at >= DATE '2025-07-05' AND captured_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS captured_prior_eur
    FROM (
      SELECT p.tenant_id AS tenant_id, p.captured_at AS captured_at,
             CAST(p.amount / COALESCE(er.rate, 1) AS NUMERIC(18,6)) AS amount_eur
      FROM payments p
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS date)
      WHERE p.status = 'captured'
        AND p.captured_at >= DATE '2025-07-05' AND p.captured_at < DATE '2026-01-01'
    ) ca_inner
    GROUP BY tenant_id
  ) ca ON ca.tenant_id = ra.tenant_id
) rr ON rr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         CAST(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END) AS decimal(18,6))
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' THEN 1 ELSE 0 END), 0) AS cancel_rate_last,
         CAST(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END) AS decimal(18,6))
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' THEN 1 ELSE 0 END), 0) AS cancel_rate_prior
  FROM orders
  WHERE placed_at >= DATE '2025-07-05' AND placed_at < DATE '2026-01-01'
  GROUP BY tenant_id
) cr ON cr.tenant_id = t.id
LEFT JOIN (
  SELECT i.tenant_id AS tenant_id,
         SUM(CAST(i.amount_due / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS overdue_eur
  FROM invoices i
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = i.currency_code
   AND er.as_of = (
        SELECT MAX(er2.as_of) FROM exchange_rates er2
        WHERE er2.base_code = 'EUR' AND er2.quote_code = i.currency_code AND er2.as_of <= DATE '2026-01-05'
      )
  WHERE i.status = 'past_due' OR (i.status = 'open' AND i.due_at < DATE '2026-01-05')
  GROUP BY i.tenant_id
) ov ON ov.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN started_at >= DATE '2025-10-03' AND started_at < DATE '2026-01-01' THEN 1 ELSE 0 END) AS sessions_last,
         SUM(CASE WHEN started_at >= DATE '2025-07-05' AND started_at < DATE '2025-10-03' THEN 1 ELSE 0 END) AS sessions_prior
  FROM web_sessions
  WHERE started_at >= DATE '2025-07-05' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) ws ON ws.tenant_id = t.id
ORDER BY overdue_invoice_amount_eur DESC NULLS FIRST, refund_rate_last_90d_pct DESC NULLS FIRST, t.id NULLS LAST
LIMIT 50

Firebolt{ "errors": [ { "description": "Line 25, Column 29: 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": "8718f7d4-4050-488f-8415-0411a6301d11", "query_lab
DataFusionError during planning: Correlated scalar subquery can only be used in Projection, Filter, Aggregate plan nodes
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table5.amount Decimal(14, 4) Decimal64(size = 0), coalesce(__table6.rate, 1_UInt8) Decimal(18, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

Felix's whole session, 7 turns

The request, as Felix received it

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

Title: Merchant risk signals: refunds, cancellations, unpaid invoices, shopper drop-off
Question: Which merchants show warning signs right now: rising refunds or cancellations, unpaid invoices, or falling shopper activity?
Grain: one row per merchant (tenant)
Columns:
- merchant name
- industry
- refund rate last 90 days
- refund rate prior 90 days
- cancellation rate last 90 days
- cancellation rate prior 90 days
- overdue invoice amount EUR
- web sessions last 90 days
- web sessions prior 90 days
- session growth pct
Details: Tables payment_refunds joined to payments for refund rate, equal to refund EUR divided by captured payment EUR per period. Orders for cancellation rate, equal to cancelled orders divided by all orders placed per period. Invoices for overdue amount, equal to amount_due in EUR where status is past_due, or status is open with due date already passed as of 2026-01-05. Web_sessions for session counts per period. Convert money via exchange_rates. Periods: last 90 days is Oct 3 to Dec 31 2025; prior 90 days is Jul 5 to Oct 2 2025. Order by overdue invoice amount EUR descending, tie-break refund rate last 90 days descending, then tenant_id. Cap 50 rows.

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

Felix calls dry_run

SELECT
  t.name AS merchant_name,
  t.industry,
  round(coalesce(rr.refund_rate_last, 0) * 100, 2) AS refund_rate_last_90d_pct,
  round(coalesce(rr.refund_rate_prior, 0) * 100, 2) AS refund_rate_prior_90d_pct,
  round(coalesce(cr.cancel_rate_last, 0) * 100, 2) AS cancellation_rate_last_90d_pct,
  round(coalesce(cr.cancel_rate_prior, 0) * 100, 2) AS cancellation_rate_prior_90d_pct,
  round(coalesce(ov.overdue_eur, 0), 2) AS overdue_invoice_amount_eur,
  coalesce(ws.sessions_last, 0) AS web_sessions_last_90d,
  coalesce(ws.sessions_prior, 0) AS web_sessions_prior_90d,
  round(
    CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL
         ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior
    END, 2
  ) AS session_growth_pct
FROM tenants t
LEFT JOIN (
  SELECT ra.tenant_id,
         ra.refund_last_eur / NULLIF(ca.captured_last_eur, 0) AS refund_rate_last,
         ra.refund_prior_eur / NULLIF(ca.captured_prior_eur, 0) AS refund_rate_prior
  FROM (
    SELECT tenant_id,
           SUM(CASE WHEN processed_at >= DATE '2025-10-03' AND processed_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS refund_last_eur,
           SUM(CASE WHEN processed_at >= DATE '2025-07-05' AND processed_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS refund_prior_eur
    FROM (
      SELECT pr.tenant_id, pr.processed_at,
             pr.amount / COALESCE(er.rate, 1) AS amount_eur
      FROM payment_refunds pr
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS date)
      WHERE pr.status = 'succeeded'
        AND pr.processed_at >= DATE '2025-07-05' AND pr.processed_at < DATE '2026-01-01'
    ) ra_inner
    GROUP BY tenant_id
  ) ra
  JOIN (
    SELECT tenant_id,
           SUM(CASE WHEN captured_at >= DATE '2025-10-03' AND captured_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS captured_last_eur,
           SUM(CASE WHEN captured_at >= DATE '2025-07-05' AND captured_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS captured_prior_eur
    FROM (
      SELECT p.tenant_id, p.captured_at,
             p.amount / COALESCE(er.rate, 1) AS amount_eur
      FROM payments p
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS date)
      WHERE p.status = 'captured'
        AND p.captured_at >= DATE '2025-07-05' AND p.captured_at < DATE '2026-01-01'
    ) ca_inner
    GROUP BY tenant_id
  ) ca ON ca.tenant_id = ra.tenant_id
) rr ON rr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' THEN 1 ELSE 0 END), 0) AS cancel_rate_last,
         SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' THEN 1 ELSE 0 END), 0) AS cancel_rate_prior
  FROM orders
  WHERE placed_at >= DATE '2025-07-05' AND placed_at < DATE '2026-01-01'
  GROUP BY tenant_id
) cr ON cr.tenant_id = t.id
LEFT JOIN (
  SELECT i.tenant_id,
         SUM(i.amount_due / COALESCE(er.rate, 1)) AS overdue_eur
  FROM invoices i
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = i.currency_code
   AND er.as_of = (
        SELECT MAX(er2.as_of) FROM exchange_rates er2
        WHERE er2.base_code = 'EUR' AND er2.quote_code = i.currency_code AND er2.as_of <= DATE '2026-01-05'
      )
  WHERE i.status = 'past_due' OR (i.status = 'open' AND i.due_at < DATE '2026-01-05')
  GROUP BY i.tenant_id
) ov ON ov.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN started_at >= DATE '2025-10-03' AND started_at < DATE '2026-01-01' THEN 1 ELSE 0 END) AS sessions_last,
         SUM(CASE WHEN started_at >= DATE '2025-07-05' AND started_at < DATE '2025-10-03' THEN 1 ELSE 0 END) AS sessions_prior
  FROM web_sessions
  WHERE started_at >= DATE '2025-07-05' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) ws ON ws.tenant_id = t.id
ORDER BY overdue_invoice_amount_eur DESC, refund_rate_last_90d_pct DESC, t.id
LIMIT 50

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, industry, refund_rate_last_90d_pct, refund_rate_prior_90d_pct, cancellation_rate_last_90d_pct, cancellation_rate_prior_90d_pct, overdue_invoice_amount_eur, web_sessions_last_90d, web_sessions_prior_90d, session_growth_pct

FelixQuery compiles and produces the requested columns. Submitting.

Felix calls submit

SELECT
  t.name AS merchant_name,
  t.industry,
  round(coalesce(rr.refund_rate_last, 0) * 100, 2) AS refund_rate_last_90d_pct,
  round(coalesce(rr.refund_rate_prior, 0) * 100, 2) AS refund_rate_prior_90d_pct,
  round(coalesce(cr.cancel_rate_last, 0) * 100, 2) AS cancellation_rate_last_90d_pct,
  round(coalesce(cr.cancel_rate_prior, 0) * 100, 2) AS cancellation_rate_prior_90d_pct,
  round(coalesce(ov.overdue_eur, 0), 2) AS overdue_invoice_amount_eur,
  coalesce(ws.sessions_last, 0) AS web_sessions_last_90d,
  coalesce(ws.sessions_prior, 0) AS web_sessions_prior_90d,
  round(
    CASE WHEN coalesce(ws.sessions_prior, 0) = 0 THEN NULL
         ELSE (ws.sessions_last - ws.sessions_prior) * 100.0 / ws.sessions_prior
    END, 2
  ) AS session_growth_pct
FROM tenants t
LEFT JOIN (
  SELECT ra.tenant_id,
         ra.refund_last_eur / NULLIF(ca.captured_last_eur, 0) AS refund_rate_last,
         ra.refund_prior_eur / NULLIF(ca.captured_prior_eur, 0) AS refund_rate_prior
  FROM (
    SELECT tenant_id,
           SUM(CASE WHEN processed_at >= DATE '2025-10-03' AND processed_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS refund_last_eur,
           SUM(CASE WHEN processed_at >= DATE '2025-07-05' AND processed_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS refund_prior_eur
    FROM (
      SELECT pr.tenant_id, pr.processed_at,
             pr.amount / COALESCE(er.rate, 1) AS amount_eur
      FROM payment_refunds pr
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = pr.currency_code AND er.as_of = CAST(pr.processed_at AS date)
      WHERE pr.status = 'succeeded'
        AND pr.processed_at >= DATE '2025-07-05' AND pr.processed_at < DATE '2026-01-01'
    ) ra_inner
    GROUP BY tenant_id
  ) ra
  JOIN (
    SELECT tenant_id,
           SUM(CASE WHEN captured_at >= DATE '2025-10-03' AND captured_at < DATE '2026-01-01' THEN amount_eur ELSE 0 END) AS captured_last_eur,
           SUM(CASE WHEN captured_at >= DATE '2025-07-05' AND captured_at < DATE '2025-10-03' THEN amount_eur ELSE 0 END) AS captured_prior_eur
    FROM (
      SELECT p.tenant_id, p.captured_at,
             p.amount / COALESCE(er.rate, 1) AS amount_eur
      FROM payments p
      LEFT JOIN exchange_rates er
        ON er.base_code = 'EUR' AND er.quote_code = p.currency_code AND er.as_of = CAST(p.captured_at AS date)
      WHERE p.status = 'captured'
        AND p.captured_at >= DATE '2025-07-05' AND p.captured_at < DATE '2026-01-01'
    ) ca_inner
    GROUP BY tenant_id
  ) ca ON ca.tenant_id = ra.tenant_id
) rr ON rr.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-10-03' AND placed_at < DATE '2026-01-01' THEN 1 ELSE 0 END), 0) AS cancel_rate_last,
         SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' AND status = 'cancelled' THEN 1 ELSE 0 END)::decimal
           / NULLIF(SUM(CASE WHEN placed_at >= DATE '2025-07-05' AND placed_at < DATE '2025-10-03' THEN 1 ELSE 0 END), 0) AS cancel_rate_prior
  FROM orders
  WHERE placed_at >= DATE '2025-07-05' AND placed_at < DATE '2026-01-01'
  GROUP BY tenant_id
) cr ON cr.tenant_id = t.id
LEFT JOIN (
  SELECT i.tenant_id,
         SUM(i.amount_due / COALESCE(er.rate, 1)) AS overdue_eur
  FROM invoices i
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = i.currency_code
   AND er.as_of = (
        SELECT MAX(er2.as_of) FROM exchange_rates er2
        WHERE er2.base_code = 'EUR' AND er2.quote_code = i.currency_code AND er2.as_of <= DATE '2026-01-05'
      )
  WHERE i.status = 'past_due' OR (i.status = 'open' AND i.due_at < DATE '2026-01-05')
  GROUP BY i.tenant_id
) ov ON ov.tenant_id = t.id
LEFT JOIN (
  SELECT tenant_id,
         SUM(CASE WHEN started_at >= DATE '2025-10-03' AND started_at < DATE '2026-01-01' THEN 1 ELSE 0 END) AS sessions_last,
         SUM(CASE WHEN started_at >= DATE '2025-07-05' AND started_at < DATE '2025-10-03' THEN 1 ELSE 0 END) AS sessions_prior
  FROM web_sessions
  WHERE started_at >= DATE '2025-07-05' AND started_at < DATE '2026-01-01'
  GROUP BY tenant_id
) ws ON ws.tenant_id = t.id
ORDER BY overdue_invoice_amount_eur DESC, refund_rate_last_90d_pct DESC, t.id
LIMIT 50

The warehouse answers

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

FelixSubmitted. The report joins five period-scoped subqueries (refunds, captured payments, order cancellations, overdue invoices, web sessions) back to `tenants`, converts all money to EUR via same-day `exchange_rates`, and computes the requested rates and growth percentage, ordered by overdue EUR descending.

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

Felix's transcript (289 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 risk signals: refunds, cancellations, unpaid invoices, shopper drop-off

Samuel Whitcombe writes to Arjun Patel

Arjun Patel plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q064

Everything, as recorded