Queries · q305 · Head of Data · week 2, night 3

Diagnose zero-value B2B deal tenants in native currency

“Are the 6 B2B-CRM tenants showing exactly 0.0 revenue/contract value/receivables genuinely inactive, or is this a currency-conversion or join artifact hiding real activity?”

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 SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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 3, request 1 of 3
Last night's B2B report raised a real data-integrity flag I need to resolve before I can trust the 38.9%-of-revenue materiality number: 6 of the 9 tenants with CRM deal activity (Ballycorran Industrial Supply, Hanse Industriebedarf, Kontor24 Burobedarf, Midland Safety Wear, Papeterie Grossiste, Polder Packaging) show exactly 0.0 across contract value, 2025 revenue, AND open receivables simultaneously, despite deal counts from 6 to 136 - that pattern (three independent measures all exactly zero together) looks like a conversion or join failure, not genuine zero business activity, and I want to check the raw native-currency numbers before concluding anything. Separately, the age-bucket dip and payment-method report together close out Rosa's timing question cleanly: bank_transfer payments average 15.7 days to capture (only 61.1% within 14 days) versus instant capture for card/wallet/credit, which fully explains the mid-range dip as pure timing, not a structural hole - I'm confident enough in that to not spend another slot on it. The segments/cohorts report found no company-wide "active customer" definition already in use (these are per-merchant marketing audiences, not Tidewick's own customer count), but it did validate that a recency-based "ordered in the last N days" concept (the "Lapsed 90 days" segment) is the operational analog marketing already relies on - good enough to write up without further digging. Tonight's three slots: (1) diagnose the zero-value B2B tenant anomaly in native currency with no FX conversion, (2) pull Castell Fasteners' actual open invoices since my €14,481 figure doesn't match Merchant Success's reported €5,080 and the board pack can't show two different numbers for the same merchant, (3) check whether Cragsley Outfitters' outsized share of late refunds is just proportional to its size or a real outlier, by comparing late-refund share against each top merchant's own total refund volume.
Diagnose zero-value B2B deal tenants in native currency
Question
Are the 6 B2B-CRM tenants showing exactly 0.0 revenue/contract value/receivables genuinely inactive, or is this a currency-conversion or join artifact hiding real activity?
One row per
tenant
Columns
- tenant_name
- tenant_default_currency_code
- deal_currency_codes
- deal_count
- deals_with_positive_value_count
- raw_contract_value_total
- raw_deal_line_items_total
- orders_count_2025
- payments_captured_count_2025

Grace 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)

WITH deal_tenants AS (
  SELECT DISTINCT tenant_id FROM deals
),
deal_summary AS (
  SELECT tenant_id,
    string_agg(DISTINCT currency_code, ',') AS deal_currency_codes,
    count(*) AS deal_count,
    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,
    sum(contract_value) AS raw_contract_value_total
  FROM deals
  GROUP BY tenant_id
),
line_items_summary AS (
  SELECT tenant_id,
    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total
  FROM deal_line_items
  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
orders_summary AS (
  SELECT tenant_id, count(*) AS orders_count_2025
  FROM orders
  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
payments_summary AS (
  SELECT tenant_id, count(*) AS payments_captured_count_2025
  FROM payments
  WHERE status IN ('captured','refunded','partially_refunded')
    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
)
SELECT
  t.name AS tenant_name,
  t.default_currency_code AS tenant_default_currency_code,
  ds.deal_currency_codes,
  ds.deal_count,
  ds.deals_with_positive_value_count,
  ds.raw_contract_value_total,
  li.raw_deal_line_items_total,
  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,
  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025
FROM deal_summary ds
JOIN tenants t ON t.id = ds.tenant_id
LEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_id
LEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_id
LEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_id
ORDER BY ds.deal_count DESC, t.name

“Compiles cleanly with all 9 requested columns at tenant 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 joins deals (and deal_line_items) against tenants, orders, and payments for the 9 tenants that have any deals row, giving Yuki the raw native-currency totals plus 2025 order/payment activity counts needed to tell whether the 6 zero-value tenants are truly inactive or just showing a conversion/join artifact.”

The report comes back overnight

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

tenant_name                   | tenant_default_currency_code | deal_currency_codes | deal_count | deals_with_positive_value_count | raw_contract_value_total | raw_deal_line_items_total | orders_count_2025 | payments_captured_count_2025
------------------------------+------------------------------+---------------------+------------+---------------------------------+--------------------------+---------------------------+-------------------+-----------------------------
Ballycorran Industrial Supply | EUR                          | EUR                 |        136 |                             136 |              575400.0000 |          4213971.15300000 |               344 |                          329
Kontor24 Bürobedarf           | EUR                          | EUR                 |        114 |                             114 |             1202700.0000 |         12268715.23800000 |               264 |                          258
OfficeHub Nordic              | NOK                          | NOK                 |         78 |                              78 |             7727000.0000 |         91967858.50000000 |               170 |                          167
Midland Safety Wear           | EUR                          | EUR                 |         77 |                              77 |              448600.0000 |          4104679.40400000 |               144 |                          134
Castell Fasteners             | USD                          | USD                 |         68 |                              68 |              644900.0000 |          3387103.99100000 |               159 |                          154
Hanse Industriebedarf         | EUR                          | EUR                 |         32 |                              32 |              215400.0000 |          1170888.52400000 |                72 |                           72
Polder Packaging              | EUR                          | EUR                 |         21 |                              21 |              107400.0000 |           687934.85400000 |                55 |                           52
Deskline Supplies             | GBP                          | GBP                 |         11 |                              11 |              101100.0000 |          1661629.57200000 |                75 |                           72
Papeterie Grossiste           | EUR                          | EUR                 |          6 |                               6 |               46100.0000 |           881720.60100000 |                18 |                           17
(9 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:
- q308Corrected 2025 revenue and receivables by B2B tenant
It builds on:
- q302B2B wholesale tenants identified by CRM deal activity, with revenue and receivables

It becomes benchmark query q305

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

determinismaggregate order
determinismtie-breaker: tenant names are not unique
made explicita selected t.col gets AS col, its standard name made explicit (5×)
placeholderstring agg
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

WITH deal_tenants AS (  SELECT DISTINCT tenant_id FROM deals),deal_summary AS (  SELECT tenant_id,    string_agg(DISTINCT currency_code, ',') AS deal_currency_codes,    {{string_agg_distinct_sorted(currency_code, ',')}} AS deal_currency_codes,    count(*) AS deal_count,    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,    sum(contract_value) AS raw_contract_value_total  FROM deals  GROUP BY tenant_id),line_items_summary AS (  SELECT tenant_id,    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total  FROM deal_line_items  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)  GROUP BY tenant_id),orders_summary AS (  SELECT tenant_id, count(*) AS orders_count_2025  FROM orders  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)  GROUP BY tenant_id),payments_summary AS (  SELECT tenant_id, count(*) AS payments_captured_count_2025  FROM payments  WHERE status IN ('captured','refunded','partially_refunded')    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)  GROUP BY tenant_id)SELECT  t.name AS tenant_name,  t.default_currency_code AS tenant_default_currency_code,  ds.deal_currency_codes,  ds.deal_count,  ds.deals_with_positive_value_count,  ds.raw_contract_value_total,  li.raw_deal_line_items_total,  ds.deal_currency_codes AS deal_currency_codes,  ds.deal_count AS deal_count,  ds.deals_with_positive_value_count AS deals_with_positive_value_count,  ds.raw_contract_value_total AS raw_contract_value_total,  li.raw_deal_line_items_total AS raw_deal_line_items_total,  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025FROM deal_summary dsJOIN tenants t ON t.id = ds.tenant_idLEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_idLEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_idLEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_idORDER BY ds.deal_count DESC, t.nameORDER BY ds.deal_count DESC NULLS FIRST, t.name NULLS LAST, ds.tenant_id NULLS LAST

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

WITH deal_tenants AS (
  SELECT DISTINCT tenant_id FROM deals
),
deal_summary AS (
  SELECT tenant_id,
    STRING_AGG(DISTINCT currency_code, ',' ORDER BY currency_code) AS deal_currency_codes,
    count(*) AS deal_count,
    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,
    sum(contract_value) AS raw_contract_value_total
  FROM deals
  GROUP BY tenant_id
),
line_items_summary AS (
  SELECT tenant_id,
    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total
  FROM deal_line_items
  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
orders_summary AS (
  SELECT tenant_id, count(*) AS orders_count_2025
  FROM orders
  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
payments_summary AS (
  SELECT tenant_id, count(*) AS payments_captured_count_2025
  FROM payments
  WHERE status IN ('captured','refunded','partially_refunded')
    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
)
SELECT
  t.name AS tenant_name,
  t.default_currency_code AS tenant_default_currency_code,
  ds.deal_currency_codes AS deal_currency_codes,
  ds.deal_count AS deal_count,
  ds.deals_with_positive_value_count AS deals_with_positive_value_count,
  ds.raw_contract_value_total AS raw_contract_value_total,
  li.raw_deal_line_items_total AS raw_deal_line_items_total,
  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,
  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025
FROM deal_summary ds
JOIN tenants t ON t.id = ds.tenant_id
LEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_id
LEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_id
LEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_id
ORDER BY ds.deal_count DESC NULLS FIRST, t.name NULLS LAST, ds.tenant_id NULLS LAST

WITH deal_tenants AS (
  SELECT DISTINCT tenant_id FROM deals
),
deal_summary AS (
  SELECT tenant_id,
    CASE WHEN COUNT(currency_code) = 0 THEN NULL ELSE ARRAY_TO_STRING(ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(currency_code))), ',') END AS deal_currency_codes,
    count(*) AS deal_count,
    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,
    sum(contract_value) AS raw_contract_value_total
  FROM deals
  GROUP BY tenant_id
),
line_items_summary AS (
  SELECT tenant_id,
    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total
  FROM deal_line_items
  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
orders_summary AS (
  SELECT tenant_id, count(*) AS orders_count_2025
  FROM orders
  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
payments_summary AS (
  SELECT tenant_id, count(*) AS payments_captured_count_2025
  FROM payments
  WHERE status IN ('captured','refunded','partially_refunded')
    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
)
SELECT
  t.name AS tenant_name,
  t.default_currency_code AS tenant_default_currency_code,
  ds.deal_currency_codes AS deal_currency_codes,
  ds.deal_count AS deal_count,
  ds.deals_with_positive_value_count AS deals_with_positive_value_count,
  ds.raw_contract_value_total AS raw_contract_value_total,
  li.raw_deal_line_items_total AS raw_deal_line_items_total,
  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,
  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025
FROM deal_summary ds
JOIN tenants t ON t.id = ds.tenant_id
LEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_id
LEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_id
LEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_id
ORDER BY ds.deal_count DESC NULLS FIRST, t.name NULLS LAST, ds.tenant_id NULLS LAST

WITH deal_tenants AS (
  SELECT DISTINCT tenant_id FROM deals
),
deal_summary AS (
  SELECT tenant_id,
    arrayStringConcat(arraySort(groupUniqArray(currency_code)), ',') AS deal_currency_codes,
    count(*) AS deal_count,
    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,
    sum(contract_value) AS raw_contract_value_total
  FROM deals
  GROUP BY tenant_id
),
line_items_summary AS (
  SELECT tenant_id,
    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total
  FROM deal_line_items
  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
orders_summary AS (
  SELECT tenant_id, count(*) AS orders_count_2025
  FROM orders
  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
payments_summary AS (
  SELECT tenant_id, count(*) AS payments_captured_count_2025
  FROM payments
  WHERE status IN ('captured','refunded','partially_refunded')
    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
)
SELECT
  t.name AS tenant_name,
  t.default_currency_code AS tenant_default_currency_code,
  ds.deal_currency_codes AS deal_currency_codes,
  ds.deal_count AS deal_count,
  ds.deals_with_positive_value_count AS deals_with_positive_value_count,
  ds.raw_contract_value_total AS raw_contract_value_total,
  li.raw_deal_line_items_total AS raw_deal_line_items_total,
  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,
  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025
FROM deal_summary ds
JOIN tenants t ON t.id = ds.tenant_id
LEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_id
LEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_id
LEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_id
ORDER BY ds.deal_count DESC NULLS FIRST, t.name NULLS LAST, ds.tenant_id NULLS LAST

Firebolt{ "errors": [ { "description": "Line 15, Column 31: 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": "1e1d7851-f320-4fcc-9d9a-8ab5ec9ffa45", "query_lab

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

Grace's whole session, 7 turns

The request, as Grace received it

Report request from Yuki Tanaka (analyst for the Head of Data):

Title: Diagnose zero-value B2B deal tenants in native currency
Question: Are the 6 B2B-CRM tenants showing exactly 0.0 revenue/contract value/receivables genuinely inactive, or is this a currency-conversion or join artifact hiding real activity?
Grain: tenant
Columns:
- tenant_name
- tenant_default_currency_code
- deal_currency_codes
- deal_count
- deals_with_positive_value_count
- raw_contract_value_total
- raw_deal_line_items_total
- orders_count_2025
- payments_captured_count_2025
Details: Population: the same 9 tenants that have at least one row in the deals table (as in last night's report). For each: tenant_default_currency_code = tenants.default_currency_code. deal_currency_codes = a comma-separated list (string_agg, distinct) of deals.currency_code values used by that tenant's deals. deal_count = count of deals rows. deals_with_positive_value_count = count of those deals where contract_value > 0. raw_contract_value_total = sum of deals.contract_value with NO currency conversion (plain native-currency sum; if multiple currencies appear for one tenant, note this but still sum raw). raw_deal_line_items_total = sum of (deal_line_items.quantity * deal_line_items.unit_price * (1 - deal_line_items.discount_pct)) across all deal_line_items belonging to this tenant's deals, also with no currency conversion, as a cross-check against raw_contract_value_total. orders_count_2025 = count of this tenant's orders (any status) placed in 2025, to check if the tenant has any order activity at all. payments_captured_count_2025 = count of this tenant's payments with status in ('captured','refunded','partially_refunded') and captured_at in 2025. Tables: deals, deal_line_items, tenants, orders, payments. Order by deal_count descending.

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

Grace calls dry_run

WITH deal_tenants AS (
  SELECT DISTINCT tenant_id FROM deals
),
deal_summary AS (
  SELECT tenant_id,
    string_agg(DISTINCT currency_code, ',') AS deal_currency_codes,
    count(*) AS deal_count,
    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,
    sum(contract_value) AS raw_contract_value_total
  FROM deals
  GROUP BY tenant_id
),
line_items_summary AS (
  SELECT tenant_id,
    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total
  FROM deal_line_items
  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
orders_summary AS (
  SELECT tenant_id, count(*) AS orders_count_2025
  FROM orders
  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
payments_summary AS (
  SELECT tenant_id, count(*) AS payments_captured_count_2025
  FROM payments
  WHERE status IN ('captured','refunded','partially_refunded')
    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
)
SELECT
  t.name AS tenant_name,
  t.default_currency_code AS tenant_default_currency_code,
  ds.deal_currency_codes,
  ds.deal_count,
  ds.deals_with_positive_value_count,
  ds.raw_contract_value_total,
  li.raw_deal_line_items_total,
  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,
  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025
FROM deal_summary ds
JOIN tenants t ON t.id = ds.tenant_id
LEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_id
LEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_id
LEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_id
ORDER BY ds.deal_count DESC, t.name

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, tenant_default_currency_code, deal_currency_codes, deal_count, deals_with_positive_value_count, raw_contract_value_total, raw_deal_line_items_total, orders_count_2025, payments_captured_count_2025

GraceCompiles cleanly with all 9 requested columns at tenant grain. Submitting.

Grace calls submit

WITH deal_tenants AS (
  SELECT DISTINCT tenant_id FROM deals
),
deal_summary AS (
  SELECT tenant_id,
    string_agg(DISTINCT currency_code, ',') AS deal_currency_codes,
    count(*) AS deal_count,
    count(*) FILTER (WHERE contract_value > 0) AS deals_with_positive_value_count,
    sum(contract_value) AS raw_contract_value_total
  FROM deals
  GROUP BY tenant_id
),
line_items_summary AS (
  SELECT tenant_id,
    sum(quantity * unit_price * (1 - discount_pct)) AS raw_deal_line_items_total
  FROM deal_line_items
  WHERE tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
orders_summary AS (
  SELECT tenant_id, count(*) AS orders_count_2025
  FROM orders
  WHERE placed_at >= DATE '2025-01-01' AND placed_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
),
payments_summary AS (
  SELECT tenant_id, count(*) AS payments_captured_count_2025
  FROM payments
  WHERE status IN ('captured','refunded','partially_refunded')
    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
    AND tenant_id IN (SELECT tenant_id FROM deal_tenants)
  GROUP BY tenant_id
)
SELECT
  t.name AS tenant_name,
  t.default_currency_code AS tenant_default_currency_code,
  ds.deal_currency_codes,
  ds.deal_count,
  ds.deals_with_positive_value_count,
  ds.raw_contract_value_total,
  li.raw_deal_line_items_total,
  COALESCE(os.orders_count_2025, 0) AS orders_count_2025,
  COALESCE(ps.payments_captured_count_2025, 0) AS payments_captured_count_2025
FROM deal_summary ds
JOIN tenants t ON t.id = ds.tenant_id
LEFT JOIN line_items_summary li ON li.tenant_id = ds.tenant_id
LEFT JOIN orders_summary os ON os.tenant_id = ds.tenant_id
LEFT JOIN payments_summary ps ON ps.tenant_id = ds.tenant_id
ORDER BY ds.deal_count DESC, t.name

The warehouse answers

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

GraceSubmitted. The report joins deals (and deal_line_items) against tenants, orders, and payments for the 9 tenants that have any deals row, giving Yuki the raw native-currency totals plus 2025 order/payment activity counts needed to tell whether the 6 zero-value tenants are truly inactive or just showing a conversion/join artifact.

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

Grace's transcript (267 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 (8 KB) · the week's memo (3 KB)

Diagnose zero-value B2B deal tenants in native currency

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

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Grace writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q305

Everything, as recorded