Queries · q309 · Head of Data · week 2, night 4

Scope of the EUR exchange-rate join bug on 2025 revenue

“If a query naively joins exchange_rates for EUR the same way it does for every other currency, how much revenue silently vanishes, and is EUR the only currency affected?”

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 4, request 2 of 3

   Tonight's diagnosis solved the B2B mystery and it's bigger than expected. The native-currency report shows all 6 "zero" tenants have real, substantial activity (e.g. Ballycorran Industrial Supply: EUR, 136 real deals worth EUR575,400, 344 orders, 329 captured payments in 2025) - and the one thing all 6 broken tenants share is currency_code = EUR, while the 3 tenants that worked fine two nights ago are NOK, USD and GBP. That is conclusive: the prior query's EUR conversion silently dropped these tenants entirely, almost certainly via a join to exchange_rates for an EUR/EUR identity row that doesn't exist (several other engineers' notes this month explicitly called out and guarded against this exact gap, so it's a known but inconsistently-handled edge case, not a one-off fluke). This means my "B2B wholesale = 38.9% of revenue" number from two nights ago is a real understatement, not just imprecise, since it's missing two-thirds of the B2B tenant list entirely. The Castell Fasteners invoice detail reconciles reasonably well with Merchant Success's figure once you separate "total open AR" (EUR14,481) from "genuinely past due" (EUR~3,450, matching my 23.8% overdue share) - the remaining gap to their EUR5,080 is most likely snapshot-timing, not a real inconsistency, and I don't think it's worth another slot. The refund-share report shows Cragsley Outfitters refunds late at 94.2% vs an 83.7% company average - elevated but in the same range as several other retail peers (Velvet Ash 94.3%, Cinder Peak 93.7%), so it reads as "big merchant, slightly-above-average rate" rather than a uniquely broken merchant - closing that question out. Tonight's three slots: (1) redo the B2B revenue/receivables numbers with EUR correctly treated as identity (rate=1.0, no join needed) to get the real materiality figure, (2) quantify how much company-wide revenue this exact bug class would silently drop if it recurred, as hard evidence for Rosa's currency-handling question, (3) produce the final, corrected B2B receivables aging table for the board pack.

   Read the whole plan
   Scope of the EUR exchange-rate join bug on 2025 revenue

   Question

   If a query naively joins exchange_rates for EUR the same way it does for every other currency, how much revenue silently vanishes, and is EUR the only currency affected?

   One row per

   currency code

   Columns

   • currency_code
   • total_2025_revenue_naive_eur
   • total_2025_revenue_correct_eur
   • shortfall_eur
   • shortfall_pct

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)

   WITH pop AS (
     SELECT currency_code, amount, captured_at
     FROM payments
     WHERE status IN ('captured','refunded','partially_refunded')
       AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
   ),
   naive AS (
     SELECT p.currency_code, SUM(p.amount / er.rate) AS naive_eur
     FROM pop p
     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)
     GROUP BY p.currency_code
   ),
   correct AS (
     SELECT p.currency_code,
       SUM(CASE WHEN p.currency_code = 'EUR' THEN p.amount ELSE p.amount / er.rate END) AS correct_eur
     FROM pop 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)
     GROUP BY p.currency_code
   )
   SELECT
     c.currency_code,
     COALESCE(n.naive_eur, 0) AS total_2025_revenue_naive_eur,
     c.correct_eur AS total_2025_revenue_correct_eur,
     c.correct_eur - COALESCE(n.naive_eur, 0) AS shortfall_eur,
     ROUND((c.correct_eur - COALESCE(n.naive_eur, 0)) / c.correct_eur * 100, 1) AS shortfall_pct
   FROM correct c
   LEFT JOIN naive n ON n.currency_code = c.currency_code
   ORDER BY c.correct_eur DESC, c.currency_code

   “Query compiles and returns 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 computes, per currency code, the naive EUR total (inner-joining exchange_rates on base_code='EUR', which silently drops any currency lacking a same-day rate row — EUR itself always lacks one since there's no EUR→EUR rate stored) versus the correct EUR total (EUR passed through at 1.0, others converted normally), plus the absolute and percentage shortfall. This will show whether EUR is the only currency with a non-zero shortfall or whether other currencies also have gaps in exchange_rates.”

5. The report comes back overnight

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

   currency_code | total_2025_revenue_naive_eur | total_2025_revenue_correct_eur |          shortfall_eur | shortfall_pct
   --------------+------------------------------+--------------------------------+------------------------+--------------
   EUR           |                          0.0 |              3099511.380000115 |      3099511.380000115 |         100.0
   USD           |           1300583.0611276545 |              1300583.061127654 | -4.656612873077393e-10 |          -0.0
   GBP           |            893904.2672557983 |              893904.2672557984 | 1.1641532182693481e-10 |           0.0
   NOK           |            453506.7042973397 |              453506.7042973398 |  5.820766091346741e-11 |           0.0
   DKK           |             82859.0331042957 |               82859.0331042957 |                    0.0 |           0.0
   SEK           |            80103.30381810686 |              80103.30381810686 |                    0.0 |           0.0
   (6 rows)

6. What came of it

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

   It builds on:

   • q308Corrected 2025 revenue and receivables by B2B tenant

7. It becomes benchmark query q309

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
   • made explicita selected t.col gets AS col, its standard name made explicit (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

   WITH pop AS (  SELECT currency_code, amount, captured_at  FROM payments  WHERE status IN ('captured','refunded','partially_refunded')    AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'),naive AS (  SELECT p.currency_code, SUM(p.amount / er.rate) AS naive_eur  SELECT p.currency_code AS currency_code, SUM(CAST(p.amount / er.rate AS NUMERIC(18,6))) AS naive_eur  FROM pop p  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)  GROUP BY p.currency_code),correct AS (  SELECT p.currency_code,    SUM(CASE WHEN p.currency_code = 'EUR' THEN p.amount ELSE p.amount / er.rate END) AS correct_eur  SELECT p.currency_code AS currency_code,    SUM(CASE WHEN p.currency_code = 'EUR' THEN p.amount ELSE CAST(p.amount / er.rate AS NUMERIC(18,6)) END) AS correct_eur  FROM pop 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)  GROUP BY p.currency_code)SELECT  c.currency_code,  c.currency_code AS currency_code,  COALESCE(n.naive_eur, 0) AS total_2025_revenue_naive_eur,  c.correct_eur AS total_2025_revenue_correct_eur,  c.correct_eur - COALESCE(n.naive_eur, 0) AS shortfall_eur,  ROUND((c.correct_eur - COALESCE(n.naive_eur, 0)) / c.correct_eur * 100, 1) AS shortfall_pct  (c.correct_eur - COALESCE(n.naive_eur, 0)) / c.correct_eur * 100 AS shortfall_pctFROM correct cLEFT JOIN naive n ON n.currency_code = c.currency_codeORDER BY c.correct_eur DESC, c.currency_codeORDER BY c.correct_eur DESC NULLS FIRST, c.currency_code 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

   WITH pop AS (
     SELECT currency_code, amount, captured_at
     FROM payments
     WHERE status IN ('captured','refunded','partially_refunded')
       AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
   ),
   naive AS (
     SELECT p.currency_code AS currency_code, SUM(CAST(p.amount / er.rate AS NUMERIC(18,6))) AS naive_eur
     FROM pop p
     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)
     GROUP BY p.currency_code
   ),
   correct AS (
     SELECT p.currency_code AS currency_code,
       SUM(CASE WHEN p.currency_code = 'EUR' THEN p.amount ELSE CAST(p.amount / er.rate AS NUMERIC(18,6)) END) AS correct_eur
     FROM pop 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)
     GROUP BY p.currency_code
   )
   SELECT
     c.currency_code AS currency_code,
     COALESCE(n.naive_eur, 0) AS total_2025_revenue_naive_eur,
     c.correct_eur AS total_2025_revenue_correct_eur,
     c.correct_eur - COALESCE(n.naive_eur, 0) AS shortfall_eur,
     (c.correct_eur - COALESCE(n.naive_eur, 0)) / c.correct_eur * 100 AS shortfall_pct
   FROM correct c
   LEFT JOIN naive n ON n.currency_code = c.currency_code
   ORDER BY c.correct_eur DESC NULLS FIRST, c.currency_code NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 8, Column 62: 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": "b3d2fd58-428a-4e19-91c0-f1f722cd68f1", "query_labe
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table2.amount Decimal(14, 4) Decimal64(size = 0), __table4.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

   Scale factor 1000, c8a.24xlarge, after the last load. q309 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: Scope of the EUR exchange-rate join bug on 2025 revenue
     Question: If a query naively joins exchange_rates for EUR the same way it does for every other currency, how much revenue silently vanishes, and is EUR the only currency affected?
     Grain: currency code
     Columns:
     - currency_code
     - total_2025_revenue_naive_eur
     - total_2025_revenue_correct_eur
     - shortfall_eur
     - shortfall_pct
     Details: Population: all payments with status in ('captured','refunded','partially_refunded') and captured_at in 2025, grouped by payments.currency_code. total_2025_revenue_naive_eur = sum of payments.amount converted to EUR using a plain INNER JOIN to exchange_rates on base_code='EUR', quote_code=payments.currency_code, as_of=captured_at date, with NO special-casing for EUR (i.e. if currency_code='EUR' and no matching exchange_rates row exists for base_code='EUR' and quote_code='EUR', those rows are simply excluded from the sum by the inner join, exactly as a careless query would behave). total_2025_revenue_correct_eur = the same sum but computed correctly (EUR amounts passed through unconverted at rate 1.0; other currencies converted via exchange_rates as normal). shortfall_eur = total_2025_revenue_correct_eur minus total_2025_revenue_naive_eur. shortfall_pct = shortfall_eur / total_2025_revenue_correct_eur * 100, rounded to 1 decimal. Tables: payments, exchange_rates. Order by total_2025_revenue_correct_eur descending.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Omar calls dry_run

     WITH pop AS (
       SELECT currency_code, amount, captured_at
       FROM payments
       WHERE status IN ('captured','refunded','partially_refunded')
         AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
     ),
     naive AS (
       SELECT p.currency_code, SUM(p.amount / er.rate) AS naive_eur
       FROM pop p
       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)
       GROUP BY p.currency_code
     ),
     correct AS (
       SELECT p.currency_code,
         SUM(CASE WHEN p.currency_code = 'EUR' THEN p.amount ELSE p.amount / er.rate END) AS correct_eur
       FROM pop 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)
       GROUP BY p.currency_code
     )
     SELECT
       c.currency_code,
       COALESCE(n.naive_eur, 0) AS total_2025_revenue_naive_eur,
       c.correct_eur AS total_2025_revenue_correct_eur,
       c.correct_eur - COALESCE(n.naive_eur, 0) AS shortfall_eur,
       ROUND((c.correct_eur - COALESCE(n.naive_eur, 0)) / c.correct_eur * 100, 1) AS shortfall_pct
     FROM correct c
     LEFT JOIN naive n ON n.currency_code = c.currency_code
     ORDER BY c.correct_eur DESC, c.currency_code

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: currency_code, total_2025_revenue_naive_eur, total_2025_revenue_correct_eur, shortfall_eur, shortfall_pct

   • OmarQuery compiles and returns the expected columns. Submitting.
   • Omar calls submit

     WITH pop AS (
       SELECT currency_code, amount, captured_at
       FROM payments
       WHERE status IN ('captured','refunded','partially_refunded')
         AND captured_at >= DATE '2025-01-01' AND captured_at < DATE '2026-01-01'
     ),
     naive AS (
       SELECT p.currency_code, SUM(p.amount / er.rate) AS naive_eur
       FROM pop p
       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)
       GROUP BY p.currency_code
     ),
     correct AS (
       SELECT p.currency_code,
         SUM(CASE WHEN p.currency_code = 'EUR' THEN p.amount ELSE p.amount / er.rate END) AS correct_eur
       FROM pop 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)
       GROUP BY p.currency_code
     )
     SELECT
       c.currency_code,
       COALESCE(n.naive_eur, 0) AS total_2025_revenue_naive_eur,
       c.correct_eur AS total_2025_revenue_correct_eur,
       c.correct_eur - COALESCE(n.naive_eur, 0) AS shortfall_eur,
       ROUND((c.correct_eur - COALESCE(n.naive_eur, 0)) / c.correct_eur * 100, 1) AS shortfall_pct
     FROM correct c
     LEFT JOIN naive n ON n.currency_code = c.currency_code
     ORDER BY c.correct_eur DESC, c.currency_code

   • The warehouse answers

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

   • OmarSubmitted. The report computes, per currency code, the naive EUR total (inner-joining `exchange_rates` on `base_code='EUR'`, which silently drops any currency lacking a same-day rate row — EUR itself always lacks one since there's no EUR→EUR rate stored) versus the correct EUR total (EUR passed through at 1.0, others converted normally), plus the absolute and percentage shortfall. This will show whether EUR is the only currency with a non-zero shortfall or whether other currencies also have gaps in `exchange_rates`.

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