Queries · q301 · Head of Data · week 2, night 1

2025 revenue share by industry vertical

“How big a slice of our 2025 collected revenue comes from B2B wholesale merchants (industrial supplies, office equipment) versus every other vertical, so we know whether omitting their receivables line from the board pack is material?”

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

1. 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

2. 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

   Show the whole note

3. Yuki Tanaka plans the night

   The analyst · week 2, night 1, request 3 of 3

   Rosa's three questions this week are each really "is this timing, is this concentrated, and is this already solved elsewhere" - all testable with the right grain. (1) For the portfolio-level revenue gap, the order-level work showed <1-5% residual gap once refunded/partially_refunded payments are included, but that used orders aged 6+ months - I never actually tested whether that residual shrinks with age (timing) or is flat from day one (structural). An age-bucket grain since placed_at will show the ramp-up curve directly: if match rate keeps climbing past 90-180 days it's timing; if it plateaus early at e.g. 95-98% and stays there, that plateau is the real structural ceiling. (2) For refund/payout traceability, Rosa wants concentration AND trend together - I can get both from one grain (tenant) by comparing H1 vs H2 2025 late-refund euros per tenant, which also directly tests whether the problem is spreading or shrinking. (3) For the B2B sizing question, rather than rebuilding my fuzzy industry-text merchant list, I'm pulling revenue by industry vertical directly - this settles both "how big is B2B wholesale" and incidentally double-checks my prior ad hoc filter against the real industry categories. I'm holding the customer-definition-usage question (which other systems already use "ever purchased" vs "last 12 months") for tomorrow night, since it likely means digging into segments/cohorts/CRM definitions rather than a straightforward aggregate query, and I'd rather scope that precisely after seeing tonight's results.

   Read the whole plan
   2025 revenue share by industry vertical

   Question

   How big a slice of our 2025 collected revenue comes from B2B wholesale merchants (industrial supplies, office equipment) versus every other vertical, so we know whether omitting their receivables line from the board pack is material?

   One row per

   industry vertical (tenants.industry)

   Columns

   • industry
   • tenant_count
   • total_2025_money_collected_eur
   • pct_of_total_revenue

4. Omar 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
     coalesce(t.industry, '(none)') as industry,
     count(distinct p.tenant_id) as tenant_count,
     sum(p.amount / er.rate) as total_2025_money_collected_eur,
     round(
       sum(p.amount / er.rate) * 100.0 / sum(sum(p.amount / er.rate)) over (),
       1
     ) as pct_of_total_revenue
   FROM payments p
   JOIN tenants t ON t.id = p.tenant_id
   JOIN exchange_rates er
     ON er.base_code = 'EUR'
     AND er.quote_code = p.currency_code
     AND er.as_of = date(p.captured_at)
   WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
     AND p.captured_at >= timestamp '2025-01-01'
     AND p.captured_at < timestamp '2026-01-01'
   GROUP BY coalesce(t.industry, '(none)')
   ORDER BY total_2025_money_collected_eur DESC, industry

   “Looks good, submitting.”

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

   “Submitted. The report returns one row per distinct tenants.industry value (nulls as (none)), with tenant_count, EUR-converted 2025 collected revenue, and percent share — so Yuki can pull out the industrial supplies / office equipment rows directly and compare them to the rest.”

5. The report comes back overnight

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

   industry                  | tenant_count | total_2025_money_collected_eur | pct_of_total_revenue
   --------------------------+--------------+--------------------------------+---------------------
   Apparel & Footwear        |           12 |              1078095.093126067 |                 38.4
   Office Supplies (B2B)     |            2 |               707746.699374597 |                 25.2
   Industrial Supplies (B2B) |            1 |              386487.6248121912 |                 13.7
   Sports & Outdoor          |            5 |               208642.665255018 |                  7.4
   Home & Living             |            9 |             130876.13832331174 |                  4.7
   Beauty                    |            8 |              125299.2177342256 |                  4.5
   Food & Coffee             |            5 |              77195.58403267055 |                  2.7
   Pet Supplies              |            7 |              55275.88436275428 |                  2.0
   Consumer Electronics      |            3 |             32704.557149522403 |                  1.2
   Books & Stationery        |            4 |              5631.031170823513 |                  0.2
   Jewelry & Accessories     |            1 |             3001.8742620116054 |                  0.1
   (11 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q302B2B wholesale tenants identified by CRM deal activity, with revenue and receivables

7. It becomes benchmark query q301

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

   • spellingdate cast
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (3×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
   The benchmark text, against the submitted SQL

   SELECT  coalesce(t.industry, '(none)') as industry,  count(distinct p.tenant_id) as tenant_count,  sum(p.amount / er.rate) as total_2025_money_collected_eur,  round(    sum(p.amount / er.rate) * 100.0 / sum(sum(p.amount / er.rate)) over (),    1  ) as pct_of_total_revenue  sum(CAST(p.amount / er.rate AS NUMERIC(18,6))) as total_2025_money_collected_eur,  sum(CAST(p.amount / er.rate AS NUMERIC(18,6))) * 100.0 / sum(sum(CAST(p.amount / er.rate AS NUMERIC(18,6)))) over () as pct_of_total_revenueFROM payments pJOIN tenants t ON t.id = p.tenant_idJOIN exchange_rates er  ON er.base_code = 'EUR'  AND er.quote_code = p.currency_code  AND er.as_of = date(p.captured_at)  AND er.as_of = CAST(p.captured_at AS DATE)WHERE p.status IN ('captured', 'refunded', 'partially_refunded')  AND p.captured_at >= timestamp '2025-01-01'  AND p.captured_at < timestamp '2026-01-01'GROUP BY coalesce(t.industry, '(none)')ORDER BY total_2025_money_collected_eur DESC, industryORDER BY total_2025_money_collected_eur DESC NULLS FIRST, industry NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   SELECT
     coalesce(t.industry, '(none)') as industry,
     count(distinct p.tenant_id) as tenant_count,
     sum(CAST(p.amount / er.rate AS NUMERIC(18,6))) as total_2025_money_collected_eur,
     sum(CAST(p.amount / er.rate AS NUMERIC(18,6))) * 100.0 / sum(sum(CAST(p.amount / er.rate AS NUMERIC(18,6)))) over () as pct_of_total_revenue
   FROM payments p
   JOIN tenants t ON t.id = p.tenant_id
   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 IN ('captured', 'refunded', 'partially_refunded')
     AND p.captured_at >= timestamp '2025-01-01'
     AND p.captured_at < timestamp '2026-01-01'
   GROUP BY coalesce(t.industry, '(none)')
   ORDER BY total_2025_money_collected_eur DESC NULLS FIRST, industry NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 4, Column 21: 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": "38c992b6-a5a6-468e-a69c-276cf6445d5e", "query_labe
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table1.amount Decimal(14, 4) Decimal64(size = 0), __table3.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

   Scale factor 1000, c8a.24xlarge, after the last load. q301 on the start page, with every configuration.

8. 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

   Omar's whole session, 7 turns

   • The request, as Omar received it

     Report request from Yuki Tanaka (analyst for the Head of Data):
     
     Title: 2025 revenue share by industry vertical
     Question: How big a slice of our 2025 collected revenue comes from B2B wholesale merchants (industrial supplies, office equipment) versus every other vertical, so we know whether omitting their receivables line from the board pack is material?
     Grain: industry vertical (tenants.industry)
     Columns:
     - industry
     - tenant_count
     - total_2025_money_collected_eur
     - pct_of_total_revenue
     Details: Population: payments with status in ('captured','refunded','partially_refunded') and captured_at in 2025 (2025-01-01 to 2025-12-31). Join payments.tenant_id to tenants to get tenants.industry (group by the literal industry value as stored, do not pre-filter or re-bucket it - show every distinct value that appears, including null/unclassified as its own row labeled '(none)'). tenant_count = count of distinct tenants in that industry with at least one such payment in 2025. total_2025_money_collected_eur = sum of payments.amount converted to EUR via exchange_rates (base_code='EUR', quote_code=payments.currency_code, as_of=captured_at date). pct_of_total_revenue = total_2025_money_collected_eur / (sum across all industries) * 100, rounded to 1 decimal. Tables: payments, tenants, exchange_rates. Order by total_2025_money_collected_eur descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Omar calls dry_run

     SELECT
       coalesce(t.industry, '(none)') as industry,
       count(distinct p.tenant_id) as tenant_count,
       sum(p.amount / er.rate) as total_2025_money_collected_eur,
       round(
         sum(p.amount / er.rate) * 100.0 / sum(sum(p.amount / er.rate)) over (),
         1
       ) as pct_of_total_revenue
     FROM payments p
     JOIN tenants t ON t.id = p.tenant_id
     JOIN exchange_rates er
       ON er.base_code = 'EUR'
       AND er.quote_code = p.currency_code
       AND er.as_of = date(p.captured_at)
     WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
       AND p.captured_at >= timestamp '2025-01-01'
       AND p.captured_at < timestamp '2026-01-01'
     GROUP BY coalesce(t.industry, '(none)')
     ORDER BY total_2025_money_collected_eur DESC, industry

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: industry, tenant_count, total_2025_money_collected_eur, pct_of_total_revenue

   • OmarLooks good, submitting.
   • Omar calls submit

     SELECT
       coalesce(t.industry, '(none)') as industry,
       count(distinct p.tenant_id) as tenant_count,
       sum(p.amount / er.rate) as total_2025_money_collected_eur,
       round(
         sum(p.amount / er.rate) * 100.0 / sum(sum(p.amount / er.rate)) over (),
         1
       ) as pct_of_total_revenue
     FROM payments p
     JOIN tenants t ON t.id = p.tenant_id
     JOIN exchange_rates er
       ON er.base_code = 'EUR'
       AND er.quote_code = p.currency_code
       AND er.as_of = date(p.captured_at)
     WHERE p.status IN ('captured', 'refunded', 'partially_refunded')
       AND p.captured_at >= timestamp '2025-01-01'
       AND p.captured_at < timestamp '2026-01-01'
     GROUP BY coalesce(t.industry, '(none)')
     ORDER BY total_2025_money_collected_eur DESC, industry

   • The warehouse answers

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

   • OmarSubmitted. The report returns one row per distinct `tenants.industry` value (nulls as `(none)`), with tenant_count, EUR-converted 2025 collected revenue, and percent share — so Yuki can pull out the industrial supplies / office equipment rows directly and compare them to the rest.

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

   Omar's transcript (260 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 (7 KB) · the week's memo (3 KB)