Queries · q304 · Head of Data · week 2, night 2

Payment capture lag by payment method

“Does the 8-14 day dip in order-to-payment match rate trace to a specific payment method with inherently slower capture (e.g. bank transfer or wire), supporting the 'it's timing, not a hole' explanation?”

Manager · asked the questionRosa Van den BergHead of Data. Took over the analytics team last week. Before the team's numbers go to the board she wants to know how far they can be trusted.

Analyst · planned the reportYuki TanakaDetail-oriented: picks specific merchants, products or days and looks at them closely.

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

A business event lands on the Head of Data's desk
Week 1; shared with Tobias Lindqvist, Chief Financial Officer
Before the board pack goes out, Data must certify the key metrics. Do revenue, orders, customers, refunds and payouts agree wherever they are recorded, and where they do not, why not and by how much?
One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events
Rosa Van den Berg writes to Yuki Tanaka
The manager, at the start of week 2
Subject: Good work — a few things I need pinned down before we certify

Yuki,

This is exactly the kind of digging I wanted — especially catching that invoices weren't a real fifth number, and that your first "20-40% gap" read was wrong and you went back to check it. That's the instinct I want on this team.

Three things surprised me and need explaining before I can tell Tobias we're ready:
1. Even with invoices out of the picture, order revenue, payments captured and payout gross still don't match each other (332,982 vs 323,026 vs 322,182 in your December example). You've explained orders-to-payments very well at the order level — but what explains that same gap when we look at it as one portfolio total for a month? Is it simply orders placed near month-end that haven't been paid yet, or something else?
2. The refund/payout traceability gap worries me more than you've flagged it. Before we loop in engineering, I want a sense of scale and shape: is this refund-timing effect spread evenly across all merchants, or concentrated in a few — because if it's concentrated, "we can't point to which payout absorbed it" becomes a real merchant-facing payout-support problem, not just a reporting footnote.
3. On customers — I don't want to just pick two numbers ourselves. I want to understand what each of those two counts is actually used for elsewhere in the business today, so we're not inventing a new definition the rest of the company doesn't already use.
Questions for this week:
- For the revenue gap between orders and money actually collected, at the portfolio level — can you tell me how much of it is pure timing (uncollected yet) versus something that never gets collected at all?
- For the refund/payout gap — is it concentrated in particular merchants or spread thinly across all of them, and does the size of it grow or shrink over the past year?
- Between the two customer counts, which one is already being used anywhere else in the business (support, billing, marketing), so we're not asking the board to adopt a brand-new definition?
- On the B2B receivables — rather than spending a slot rebuilding the merchant list this week, can you give me a rough sense of whether wholesalers are large enough a slice of revenue that leaving that line out of Friday's pack is actually a problem?
Keep the FX-adjusted view for USD/NOK on your list — that one's solid and the board will ask about it.

Thanks for the pace you're keeping on this.

Rosa
Yuki Tanaka plans the night
The analyst · week 2, night 2, request 3 of 3
Three things from last night change my plan. (1) The age-bucket report is reassuring but not a clean monotonic ramp: match rate is 100% at 0-7 days, dips to 83.4% at 8-14 days, climbs through the 90s, then settles around 98.9-100.1% from 91 days onward - so the true permanent "never collected" floor looks like just ~1-2%, but that mid-range dip needs an explanation before I tell Rosa this is purely timing; my best guess is a payment method with inherent capture lag (bank transfer/wire), which I want to test directly. (2) The refund-concentration report confirms Rosa's worry: the top 4 merchants (Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane, Studio Vrij) account for 70.8% of all late-processed refunds in 2025, with Cragsley alone at 36.2% and still growing (+36.2% H1-to-H2) - this is genuinely concentrated, not spread thin, and it sits on our single biggest merchant. (3) The industry-revenue report was the big surprise: B2B wholesale (Office Supplies + Industrial Supplies) is 38.9% of 2025 collected revenue across just 3 tenants - far too material to leave out of the board pack, which changes the stakes on getting that receivables number right. But it also only found 3 B2B tenants by industry label, while Daniel Kim's team (last week's channel) referenced 5 B2B wholesale merchants - that gap is worth resolving properly now, by identifying B2B tenants operationally (via actual CRM deal activity) rather than by industry-text label, and recomputing their receivables on the corrected set. I'm also starting on Rosa's customer-definition-usage question tonight by surveying what audience/segment definitions already exist and are actively used elsewhere in the business.
Payment capture lag by payment method
Question
Does the 8-14 day dip in order-to-payment match rate trace to a specific payment method with inherently slower capture (e.g. bank transfer or wire), supporting the 'it's timing, not a hole' explanation?
One row per
payment method kind (payment_methods.kind)
Columns
- kind
- payment_count
- avg_days_order_to_capture
- pct_captured_within_7_days
- pct_captured_within_14_days

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.kind AS kind,
  COALESCE(t1.payment_count, 0) AS payment_count,
  t2.avg_days_order_to_capture AS avg_days_order_to_capture,
  t3.pct_captured_within_7_days AS pct_captured_within_7_days,
  t4.pct_captured_within_14_days AS pct_captured_within_14_days
FROM (SELECT DISTINCT pm0.kind AS kind FROM payment_methods pm0) t0
LEFT JOIN (
  SELECT pm1.kind AS kind, COUNT(*) AS payment_count
  FROM payments p1
  JOIN payment_methods pm1 ON p1.payment_method_id = pm1.id
  JOIN orders o1 ON p1.order_id = o1.id
  WHERE p1.status IN ('captured','refunded','partially_refunded')
    AND p1.order_id IS NOT NULL
  GROUP BY pm1.kind
) t1 ON t1.kind = t0.kind
LEFT JOIN (
  SELECT pm2.kind AS kind,
         ROUND(AVG(EXTRACT(EPOCH FROM (p2.captured_at - o2.placed_at)) / 86400.0)::numeric, 1) AS avg_days_order_to_capture
  FROM payments p2
  JOIN payment_methods pm2 ON p2.payment_method_id = pm2.id
  JOIN orders o2 ON p2.order_id = o2.id
  WHERE p2.status IN ('captured','refunded','partially_refunded')
    AND p2.order_id IS NOT NULL
    AND p2.captured_at IS NOT NULL
    AND o2.placed_at IS NOT NULL
  GROUP BY pm2.kind
) t2 ON t2.kind = t0.kind
LEFT JOIN (
  SELECT pm3.kind AS kind,
         ROUND(100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_7_days
  FROM payments p3
  JOIN payment_methods pm3 ON p3.payment_method_id = pm3.id
  JOIN orders o3 ON p3.order_id = o3.id
  WHERE p3.status IN ('captured','refunded','partially_refunded')
    AND p3.order_id IS NOT NULL
    AND p3.captured_at IS NOT NULL
    AND o3.placed_at IS NOT NULL
  GROUP BY pm3.kind
) t3 ON t3.kind = t0.kind
LEFT JOIN (
  SELECT pm4.kind AS kind,
         ROUND(100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_14_days
  FROM payments p4
  JOIN payment_methods pm4 ON p4.payment_method_id = pm4.id
  JOIN orders o4 ON p4.order_id = o4.id
  WHERE p4.status IN ('captured','refunded','partially_refunded')
    AND p4.order_id IS NOT NULL
    AND p4.captured_at IS NOT NULL
    AND o4.placed_at IS NOT NULL
  GROUP BY pm4.kind
) t4 ON t4.kind = t0.kind
WHERE COALESCE(t1.payment_count, 0) > 0
ORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASC

“Looks good — compiles cleanly with the expected 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 groups payments (status in captured/refunded/partially_refunded, linked to an order) by payment_methods.kind, with payment count, average order-to-capture days, and the 7-day/14-day capture rates — ordered slowest-to-fastest average so any method driving the 8-14 day dip stands out at the top.”

The report comes back overnight

As Yuki Tanaka saw it, from the small dataset the agents worked on

kind          | payment_count | avg_days_order_to_capture | pct_captured_within_7_days | pct_captured_within_14_days
--------------+---------------+---------------------------+----------------------------+----------------------------
bank_transfer |          5169 |                      15.7 |                       60.2 |                        61.1
card          |         25644 |                       0.0 |                      100.0 |                       100.0
credit        |          1669 |                       0.0 |                      100.0 |                       100.0
wallet        |          7458 |                       0.0 |                      100.0 |                       100.0
(4 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q299Order-to-payment match rate by order age

It becomes benchmark query q304

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

spellingcast
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
placeholderseconds between
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results

The benchmark text, against the submitted SQL

SELECT  t0.kind AS kind,  COALESCE(t1.payment_count, 0) AS payment_count,  t2.avg_days_order_to_capture AS avg_days_order_to_capture,  t3.pct_captured_within_7_days AS pct_captured_within_7_days,  t4.pct_captured_within_14_days AS pct_captured_within_14_daysFROM (SELECT DISTINCT pm0.kind AS kind FROM payment_methods pm0) t0LEFT JOIN (  SELECT pm1.kind AS kind, COUNT(*) AS payment_count  FROM payments p1  JOIN payment_methods pm1 ON p1.payment_method_id = pm1.id  JOIN orders o1 ON p1.order_id = o1.id  WHERE p1.status IN ('captured','refunded','partially_refunded')    AND p1.order_id IS NOT NULL  GROUP BY pm1.kind) t1 ON t1.kind = t0.kindLEFT JOIN (  SELECT pm2.kind AS kind,         ROUND(AVG(EXTRACT(EPOCH FROM (p2.captured_at - o2.placed_at)) / 86400.0)::numeric, 1) AS avg_days_order_to_capture         AVG({{seconds_between(o2.placed_at, p2.captured_at)}} / 86400.0) AS avg_days_order_to_capture  FROM payments p2  JOIN payment_methods pm2 ON p2.payment_method_id = pm2.id  JOIN orders o2 ON p2.order_id = o2.id  WHERE p2.status IN ('captured','refunded','partially_refunded')    AND p2.order_id IS NOT NULL    AND p2.captured_at IS NOT NULL    AND o2.placed_at IS NOT NULL  GROUP BY pm2.kind) t2 ON t2.kind = t0.kindLEFT JOIN (  SELECT pm3.kind AS kind,         ROUND(100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_7_days         100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*) AS pct_captured_within_7_days  FROM payments p3  JOIN payment_methods pm3 ON p3.payment_method_id = pm3.id  JOIN orders o3 ON p3.order_id = o3.id  WHERE p3.status IN ('captured','refunded','partially_refunded')    AND p3.order_id IS NOT NULL    AND p3.captured_at IS NOT NULL    AND o3.placed_at IS NOT NULL  GROUP BY pm3.kind) t3 ON t3.kind = t0.kindLEFT JOIN (  SELECT pm4.kind AS kind,         ROUND(100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_14_days         100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*) AS pct_captured_within_14_days  FROM payments p4  JOIN payment_methods pm4 ON p4.payment_method_id = pm4.id  JOIN orders o4 ON p4.order_id = o4.id  WHERE p4.status IN ('captured','refunded','partially_refunded')    AND p4.order_id IS NOT NULL    AND p4.captured_at IS NOT NULL    AND o4.placed_at IS NOT NULL  GROUP BY pm4.kind) t4 ON t4.kind = t0.kindWHERE COALESCE(t1.payment_count, 0) > 0ORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASCORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASC NULLS LAST

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

SELECT
  t0.kind AS kind,
  COALESCE(t1.payment_count, 0) AS payment_count,
  t2.avg_days_order_to_capture AS avg_days_order_to_capture,
  t3.pct_captured_within_7_days AS pct_captured_within_7_days,
  t4.pct_captured_within_14_days AS pct_captured_within_14_days
FROM (SELECT DISTINCT pm0.kind AS kind FROM payment_methods pm0) t0
LEFT JOIN (
  SELECT pm1.kind AS kind, COUNT(*) AS payment_count
  FROM payments p1
  JOIN payment_methods pm1 ON p1.payment_method_id = pm1.id
  JOIN orders o1 ON p1.order_id = o1.id
  WHERE p1.status IN ('captured','refunded','partially_refunded')
    AND p1.order_id IS NOT NULL
  GROUP BY pm1.kind
) t1 ON t1.kind = t0.kind
LEFT JOIN (
  SELECT pm2.kind AS kind,
         AVG(CAST(EXTRACT(EPOCH FROM (p2.captured_at - o2.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_order_to_capture
  FROM payments p2
  JOIN payment_methods pm2 ON p2.payment_method_id = pm2.id
  JOIN orders o2 ON p2.order_id = o2.id
  WHERE p2.status IN ('captured','refunded','partially_refunded')
    AND p2.order_id IS NOT NULL
    AND p2.captured_at IS NOT NULL
    AND o2.placed_at IS NOT NULL
  GROUP BY pm2.kind
) t2 ON t2.kind = t0.kind
LEFT JOIN (
  SELECT pm3.kind AS kind,
         100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*) AS pct_captured_within_7_days
  FROM payments p3
  JOIN payment_methods pm3 ON p3.payment_method_id = pm3.id
  JOIN orders o3 ON p3.order_id = o3.id
  WHERE p3.status IN ('captured','refunded','partially_refunded')
    AND p3.order_id IS NOT NULL
    AND p3.captured_at IS NOT NULL
    AND o3.placed_at IS NOT NULL
  GROUP BY pm3.kind
) t3 ON t3.kind = t0.kind
LEFT JOIN (
  SELECT pm4.kind AS kind,
         100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*) AS pct_captured_within_14_days
  FROM payments p4
  JOIN payment_methods pm4 ON p4.payment_method_id = pm4.id
  JOIN orders o4 ON p4.order_id = o4.id
  WHERE p4.status IN ('captured','refunded','partially_refunded')
    AND p4.order_id IS NOT NULL
    AND p4.captured_at IS NOT NULL
    AND o4.placed_at IS NOT NULL
  GROUP BY pm4.kind
) t4 ON t4.kind = t0.kind
WHERE COALESCE(t1.payment_count, 0) > 0
ORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASC NULLS LAST

SELECT
  t0.kind AS kind,
  COALESCE(t1.payment_count, 0) AS payment_count,
  t2.avg_days_order_to_capture AS avg_days_order_to_capture,
  t3.pct_captured_within_7_days AS pct_captured_within_7_days,
  t4.pct_captured_within_14_days AS pct_captured_within_14_days
FROM (SELECT DISTINCT pm0.kind AS kind FROM payment_methods pm0) t0
LEFT JOIN (
  SELECT pm1.kind AS kind, COUNT(*) AS payment_count
  FROM payments p1
  JOIN payment_methods pm1 ON p1.payment_method_id = pm1.id
  JOIN orders o1 ON p1.order_id = o1.id
  WHERE p1.status IN ('captured','refunded','partially_refunded')
    AND p1.order_id IS NOT NULL
  GROUP BY pm1.kind
) t1 ON t1.kind = t0.kind
LEFT JOIN (
  SELECT pm2.kind AS kind,
         AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o2.placed_at, p2.captured_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) AS avg_days_order_to_capture
  FROM payments p2
  JOIN payment_methods pm2 ON p2.payment_method_id = pm2.id
  JOIN orders o2 ON p2.order_id = o2.id
  WHERE p2.status IN ('captured','refunded','partially_refunded')
    AND p2.order_id IS NOT NULL
    AND p2.captured_at IS NOT NULL
    AND o2.placed_at IS NOT NULL
  GROUP BY pm2.kind
) t2 ON t2.kind = t0.kind
LEFT JOIN (
  SELECT pm3.kind AS kind,
         100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*) AS pct_captured_within_7_days
  FROM payments p3
  JOIN payment_methods pm3 ON p3.payment_method_id = pm3.id
  JOIN orders o3 ON p3.order_id = o3.id
  WHERE p3.status IN ('captured','refunded','partially_refunded')
    AND p3.order_id IS NOT NULL
    AND p3.captured_at IS NOT NULL
    AND o3.placed_at IS NOT NULL
  GROUP BY pm3.kind
) t3 ON t3.kind = t0.kind
LEFT JOIN (
  SELECT pm4.kind AS kind,
         100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*) AS pct_captured_within_14_days
  FROM payments p4
  JOIN payment_methods pm4 ON p4.payment_method_id = pm4.id
  JOIN orders o4 ON p4.order_id = o4.id
  WHERE p4.status IN ('captured','refunded','partially_refunded')
    AND p4.order_id IS NOT NULL
    AND p4.captured_at IS NOT NULL
    AND o4.placed_at IS NOT NULL
  GROUP BY pm4.kind
) t4 ON t4.kind = t0.kind
WHERE COALESCE(t1.payment_count, 0) > 0
ORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASC NULLS LAST

DataFusionrow 1: col 4: "62.0934139491398720375910747583656311803674" vs "100.0"; col 5: "62.8406963034884527359733684721547957556002" vs "100.0"
ClickHouseCode: 43. DB::Exception: No operation lessOrEquals between Decimal(18, 6) and IntervalDay: while executing 'FUNCTION lessOrEquals(minus(__table16.captured_at, __table18.placed_at) :: 6, _CAST(14_IntervalDay, 'IntervalDay'_String) :: 3) -> lessOrEquals(minus(__table16.captured_at, __table18.placed_at

Scale factor 1000, c8a.24xlarge, after the last load. q304 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 Yuki Tanaka (analyst for the Head of Data):

Title: Payment capture lag by payment method
Question: Does the 8-14 day dip in order-to-payment match rate trace to a specific payment method with inherently slower capture (e.g. bank transfer or wire), supporting the 'it's timing, not a hole' explanation?
Grain: payment method kind (payment_methods.kind)
Columns:
- kind
- payment_count
- avg_days_order_to_capture
- pct_captured_within_7_days
- pct_captured_within_14_days
Details: Population: payments with status in ('captured','refunded','partially_refunded'), joined to payment_methods on payments.payment_method_id = payment_methods.id to get kind (card, bank_transfer, paypal, wallet, wire, credit), and joined to orders on payments.order_id = orders.id to get placed_at (only include payments linked to an order; skip payments with a null order_id). Group by payment_methods.kind. payment_count = count of such payments. avg_days_order_to_capture = average of (payments.captured_at minus orders.placed_at) in days, rounded to 1 decimal. pct_captured_within_7_days = percentage of these payments where (captured_at - placed_at) <= 7 days, rounded to 1 decimal. pct_captured_within_14_days = percentage where (captured_at - placed_at) <= 14 days, rounded to 1 decimal. Tables: payments, payment_methods, orders. Order by avg_days_order_to_capture descending.

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

Bea calls dry_run

SELECT
  t0.kind AS kind,
  COALESCE(t1.payment_count, 0) AS payment_count,
  t2.avg_days_order_to_capture AS avg_days_order_to_capture,
  t3.pct_captured_within_7_days AS pct_captured_within_7_days,
  t4.pct_captured_within_14_days AS pct_captured_within_14_days
FROM (SELECT DISTINCT pm0.kind AS kind FROM payment_methods pm0) t0
LEFT JOIN (
  SELECT pm1.kind AS kind, COUNT(*) AS payment_count
  FROM payments p1
  JOIN payment_methods pm1 ON p1.payment_method_id = pm1.id
  JOIN orders o1 ON p1.order_id = o1.id
  WHERE p1.status IN ('captured','refunded','partially_refunded')
    AND p1.order_id IS NOT NULL
  GROUP BY pm1.kind
) t1 ON t1.kind = t0.kind
LEFT JOIN (
  SELECT pm2.kind AS kind,
         ROUND(AVG(EXTRACT(EPOCH FROM (p2.captured_at - o2.placed_at)) / 86400.0)::numeric, 1) AS avg_days_order_to_capture
  FROM payments p2
  JOIN payment_methods pm2 ON p2.payment_method_id = pm2.id
  JOIN orders o2 ON p2.order_id = o2.id
  WHERE p2.status IN ('captured','refunded','partially_refunded')
    AND p2.order_id IS NOT NULL
    AND p2.captured_at IS NOT NULL
    AND o2.placed_at IS NOT NULL
  GROUP BY pm2.kind
) t2 ON t2.kind = t0.kind
LEFT JOIN (
  SELECT pm3.kind AS kind,
         ROUND(100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_7_days
  FROM payments p3
  JOIN payment_methods pm3 ON p3.payment_method_id = pm3.id
  JOIN orders o3 ON p3.order_id = o3.id
  WHERE p3.status IN ('captured','refunded','partially_refunded')
    AND p3.order_id IS NOT NULL
    AND p3.captured_at IS NOT NULL
    AND o3.placed_at IS NOT NULL
  GROUP BY pm3.kind
) t3 ON t3.kind = t0.kind
LEFT JOIN (
  SELECT pm4.kind AS kind,
         ROUND(100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_14_days
  FROM payments p4
  JOIN payment_methods pm4 ON p4.payment_method_id = pm4.id
  JOIN orders o4 ON p4.order_id = o4.id
  WHERE p4.status IN ('captured','refunded','partially_refunded')
    AND p4.order_id IS NOT NULL
    AND p4.captured_at IS NOT NULL
    AND o4.placed_at IS NOT NULL
  GROUP BY pm4.kind
) t4 ON t4.kind = t0.kind
WHERE COALESCE(t1.payment_count, 0) > 0
ORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: kind, payment_count, avg_days_order_to_capture, pct_captured_within_7_days, pct_captured_within_14_days

BeaLooks good — compiles cleanly with the expected columns. Submitting.

Bea calls submit

SELECT
  t0.kind AS kind,
  COALESCE(t1.payment_count, 0) AS payment_count,
  t2.avg_days_order_to_capture AS avg_days_order_to_capture,
  t3.pct_captured_within_7_days AS pct_captured_within_7_days,
  t4.pct_captured_within_14_days AS pct_captured_within_14_days
FROM (SELECT DISTINCT pm0.kind AS kind FROM payment_methods pm0) t0
LEFT JOIN (
  SELECT pm1.kind AS kind, COUNT(*) AS payment_count
  FROM payments p1
  JOIN payment_methods pm1 ON p1.payment_method_id = pm1.id
  JOIN orders o1 ON p1.order_id = o1.id
  WHERE p1.status IN ('captured','refunded','partially_refunded')
    AND p1.order_id IS NOT NULL
  GROUP BY pm1.kind
) t1 ON t1.kind = t0.kind
LEFT JOIN (
  SELECT pm2.kind AS kind,
         ROUND(AVG(EXTRACT(EPOCH FROM (p2.captured_at - o2.placed_at)) / 86400.0)::numeric, 1) AS avg_days_order_to_capture
  FROM payments p2
  JOIN payment_methods pm2 ON p2.payment_method_id = pm2.id
  JOIN orders o2 ON p2.order_id = o2.id
  WHERE p2.status IN ('captured','refunded','partially_refunded')
    AND p2.order_id IS NOT NULL
    AND p2.captured_at IS NOT NULL
    AND o2.placed_at IS NOT NULL
  GROUP BY pm2.kind
) t2 ON t2.kind = t0.kind
LEFT JOIN (
  SELECT pm3.kind AS kind,
         ROUND(100.0 * SUM(CASE WHEN (p3.captured_at - o3.placed_at) <= INTERVAL '7 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_7_days
  FROM payments p3
  JOIN payment_methods pm3 ON p3.payment_method_id = pm3.id
  JOIN orders o3 ON p3.order_id = o3.id
  WHERE p3.status IN ('captured','refunded','partially_refunded')
    AND p3.order_id IS NOT NULL
    AND p3.captured_at IS NOT NULL
    AND o3.placed_at IS NOT NULL
  GROUP BY pm3.kind
) t3 ON t3.kind = t0.kind
LEFT JOIN (
  SELECT pm4.kind AS kind,
         ROUND(100.0 * SUM(CASE WHEN (p4.captured_at - o4.placed_at) <= INTERVAL '14 days' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_captured_within_14_days
  FROM payments p4
  JOIN payment_methods pm4 ON p4.payment_method_id = pm4.id
  JOIN orders o4 ON p4.order_id = o4.id
  WHERE p4.status IN ('captured','refunded','partially_refunded')
    AND p4.order_id IS NOT NULL
    AND p4.captured_at IS NOT NULL
    AND o4.placed_at IS NOT NULL
  GROUP BY pm4.kind
) t4 ON t4.kind = t0.kind
WHERE COALESCE(t1.payment_count, 0) > 0
ORDER BY t2.avg_days_order_to_capture DESC NULLS LAST, t0.kind ASC

The warehouse answers

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

BeaSubmitted. The report groups payments (status in captured/refunded/partially_refunded, linked to an order) by `payment_methods.kind`, with payment count, average order-to-capture days, and the 7-day/14-day capture rates — ordered slowest-to-fastest average so any method driving the 8-14 day dip stands out at the top.

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 (271 KB) · Yuki Tanaka's transcript (954 KB) · Rosa Van den Berg's transcript (262 KB) · the night's plan (6 KB) · the night's reports (13 KB) · the week's memo (3 KB)

Payment capture lag by payment method

A business event lands on the Head of Data's desk

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q304

Everything, as recorded