Queries · q098 · Head of Data · week 1, night 4

EUR exchange rate movement by currency, 2023 vs 2025

“How much have the currencies our merchants transact in moved against EUR over the three years of history, and is that movement large enough to distort revenue trends when we roll up multi-currency figures over time?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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 1
Subject: Certifying the key metrics before the board pack — what I need from you this week

Hi Yuki,

Tobias and I have to certify revenue, orders, customers, refunds and payouts before they go into the board pack. "Certify" means we're telling the board these numbers are reconcilable — so this week's job is to find out where the different places we record these figures agree, where they don't, and whether any gap is big enough to change the story we tell. If something doesn't reconcile, I need to know why and by how much, not just that it's "close enough." That feeds directly into the go/no-go Tobias and I give on the board pack, so please prioritize breadth across the five metrics over depth on any one of them — we can always dig deeper next week on whichever one looks shakiest.

You only have a handful of report slots a night, so here's what matters most to me, roughly in priority order:
1. If someone asks "what was our revenue last month," do we get the same answer regardless of which part of the business we ask? Where it diverges, how far apart are the answers, and is the gap explained by something sensible (timing, currency, returns) or does it look like a real inconsistency?
2. Do our order counts and our revenue tell a consistent story — i.e., is there a clean, traceable link from an order to the money that was actually collected for it, or are there orders that seem to vanish or multiply along the way?
3. Refunds and payouts: when money goes back to a customer, does it show up consistently as a reduction in revenue and in what we actually pay out to merchants? I want to know if refunds could be hiding or double-counted anywhere.
4. Customers: can we count "how many customers we have" or "how many are active" in one consistent way, or do different parts of the data give meaningfully different headcounts?
5. If you have a slot left: is there anything about how timestamps or currencies are handled that could quietly distort any of the above when we roll numbers up across time or across merchants?
Please flag anything you find inconsistent together with your best read of why, and roughly how big the discrepancy is in practice — that's what Tobias and I need to decide whether we can certify as-is, certify with a caveat, or need to hold a number back.

Thank you — I know this is a lot for one week. Let's talk before you spend your first slot tonight if you want to sanity-check the plan.

Rosa
A note from the Chief Financial Officer, Tobias Lindqvist
Subject: What I need for the revenue/payout certification

Hi Yuki,

A few things from my side as you scope this week's work:
1. Does our reported revenue tie out end to end? I want to know whether what we book as platform fees and processing fees matches what actually settles to us through payouts — and if there's a gap, roughly how large it is and whether it's timing (month-end cutoff) or something structural.
2. Do merchant payouts reconcile to the gross volume and refunds we report? I'm particularly interested in whether net payouts consistently equal gross sales minus refunds minus our fees, or whether certain merchants/currencies show persistent drift.
3. Are our B2B wholesalers' outstanding receivables consistent with what we'd show in the annual accounts? I want to understand how much is genuinely overdue versus just lagging normal payment terms, since that affects how we present them.
If anything else jumps out while you're in there — especially around currency conversion timing or refund treatment — flag it even if it's outside these three.

Thanks, Tobias
Yuki Tanaka plans the night
The analyst · week 1, night 4, request 2 of 3
Night 3 closed out two big open questions. The refund-inclusive order-status report shows the original 22% fulfilled gap was almost entirely a status-filter artifact: captured payments that later moved to refunded or partially_refunded status were invisible to a captured-only filter. Once included, the real residual gap is 0.9% for fulfilled orders and 5.3% for refunded orders, and the cancelled bucket is fully explained by orders cancelled before any charge plus clean full-refund treatment (net collected is exactly 0) for the ones that were charged. That is good news: orders trace cleanly to money collected once refund statuses are included. The refund-timing report found something genuinely structural: 85.5% of refund dollars are processed after the original payment's payout already settled, 20.9 days later on average, and payout_items has a one-row-per-payment constraint, so there is no clean slot in any payout to tie that later clawback back to the original sale. That explains the earlier volatility and is a real traceability gap to flag to Tobias rather than something I can fully resolve with one more query. The customer-headcount report shows a huge range from 11,258 total accounts down to 411 with an active subscription, with status equals active nearly meaningless as an activity signal since it covers 99.3% of all accounts. Tonight I am closing out the two asks not yet touched, Tobias's B2B receivables question and Rosa's currency and timestamp distortion question, plus one more targeted test of whether gross minus fee equals net holds consistently across currencies, tested in native currency to avoid any FX conversion noise contaminating that specific check.
EUR exchange rate movement by currency, 2023 vs 2025
Question
How much have the currencies our merchants transact in moved against EUR over the three years of history, and is that movement large enough to distort revenue trends when we roll up multi-currency figures over time?
One row per
currency code
Columns
- currency_code
- tenant_count
- orders_2025_total_eur
- rate_eur_to_currency_2023_01_02
- rate_eur_to_currency_2025_12_31
- pct_rate_change

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 currencies_used AS (
  SELECT DISTINCT currency_code FROM orders
),
tenant_counts AS (
  SELECT default_currency_code AS currency_code, COUNT(DISTINCT id) AS tenant_count
  FROM tenants
  GROUP BY default_currency_code
),
orders_2025 AS (
  SELECT o.currency_code,
    SUM(
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE o.grand_total / er.rate END
    ) AS orders_2025_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
  GROUP BY o.currency_code
),
rate_2023 AS (
  SELECT cu.currency_code,
    COALESCE(
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2023-01-02'),
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC LIMIT 1)
    ) AS rate_early
  FROM currencies_used cu
),
rate_2025 AS (
  SELECT cu.currency_code,
    (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2025-12-31') AS rate_late
  FROM currencies_used cu
)
SELECT
  cu.currency_code,
  COALESCE(tc.tenant_count,0) AS tenant_count,
  ROUND(COALESCE(o25.orders_2025_total_eur,0),2) AS orders_2025_total_eur,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r23.rate_early END AS rate_eur_to_currency_2023_01_02,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r25.rate_late END AS rate_eur_to_currency_2025_12_31,
  ROUND(
    CASE WHEN cu.currency_code='EUR' THEN 0
    ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100
    END, 1
  ) AS pct_rate_change
FROM currencies_used cu
LEFT JOIN tenant_counts tc ON tc.currency_code = cu.currency_code
LEFT JOIN orders_2025 o25 ON o25.currency_code = cu.currency_code
LEFT JOIN rate_2023 r23 ON r23.currency_code = cu.currency_code
LEFT JOIN rate_2025 r25 ON r25.currency_code = cu.currency_code
ORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_code

“Query compiles correctly with all 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 gives one row per currency_code used in orders, with tenant counts, 2025 EUR-converted order totals, the EUR→currency rates at the start and end of the three-year history, and the percent change between them — ordered by order volume descending.”

The report comes back overnight

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

currency_code | tenant_count | orders_2025_total_eur | rate_eur_to_currency_2023_01_02 | rate_eur_to_currency_2025_12_31 | pct_rate_change
--------------+--------------+-----------------------+---------------------------------+---------------------------------+----------------
EUR           |           58 |            3129100.34 |                        1.000000 |                        1.000000 |             0.0
USD           |           18 |             1260986.9 |                        1.069236 |                        1.186695 |            11.0
GBP           |           25 |             864036.92 |                        0.886173 |                        0.860807 |            -2.9
NOK           |            6 |             426199.66 |                       10.540942 |                       11.693321 |            10.9
DKK           |            5 |              80219.69 |                        7.439947 |                        7.448739 |             0.1
SEK           |            8 |              75895.03 |                       11.208926 |                       11.125129 |            -0.7
(6 rows)

It becomes benchmark query q098

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

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

The benchmark text, against the submitted SQL

WITH currencies_used AS (  SELECT DISTINCT currency_code FROM orders),tenant_counts AS (  SELECT default_currency_code AS currency_code, COUNT(DISTINCT id) AS tenant_count  FROM tenants  GROUP BY default_currency_code),orders_2025 AS (  SELECT o.currency_code,  SELECT o.currency_code AS currency_code,    SUM(      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total           ELSE o.grand_total / er.rate END           ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END    ) AS orders_2025_total_eur  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = o.currency_code   AND er.as_of = CAST(o.placed_at AS date)  WHERE o.status <> 'cancelled'    AND o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'  GROUP BY o.currency_code),rate_2023 AS (  SELECT cu.currency_code,  SELECT cu.currency_code AS currency_code,    COALESCE(      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2023-01-02'),      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC LIMIT 1)      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC NULLS LAST {{fetch_first(1)}})    ) AS rate_early  FROM currencies_used cu),rate_2025 AS (  SELECT cu.currency_code,  SELECT cu.currency_code AS currency_code,    (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2025-12-31') AS rate_late  FROM currencies_used cu)SELECT  cu.currency_code,  cu.currency_code AS currency_code,  COALESCE(tc.tenant_count,0) AS tenant_count,  ROUND(COALESCE(o25.orders_2025_total_eur,0),2) AS orders_2025_total_eur,  COALESCE(o25.orders_2025_total_eur,0) AS orders_2025_total_eur,  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r23.rate_early END AS rate_eur_to_currency_2023_01_02,  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r25.rate_late END AS rate_eur_to_currency_2025_12_31,  ROUND(    CASE WHEN cu.currency_code='EUR' THEN 0    ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100    END, 1  ) AS pct_rate_change  CASE WHEN cu.currency_code='EUR' THEN 0  ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100  END AS pct_rate_changeFROM currencies_used cuLEFT JOIN tenant_counts tc ON tc.currency_code = cu.currency_codeLEFT JOIN orders_2025 o25 ON o25.currency_code = cu.currency_codeLEFT JOIN rate_2023 r23 ON r23.currency_code = cu.currency_codeLEFT JOIN rate_2025 r25 ON r25.currency_code = cu.currency_codeORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_codeORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_code NULLS LAST

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

WITH currencies_used AS (
  SELECT DISTINCT currency_code FROM orders
),
tenant_counts AS (
  SELECT default_currency_code AS currency_code, COUNT(DISTINCT id) AS tenant_count
  FROM tenants
  GROUP BY default_currency_code
),
orders_2025 AS (
  SELECT o.currency_code AS currency_code,
    SUM(
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END
    ) AS orders_2025_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
  GROUP BY o.currency_code
),
rate_2023 AS (
  SELECT cu.currency_code AS currency_code,
    COALESCE(
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2023-01-02'),
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC NULLS LAST FETCH FIRST 1 ROWS ONLY)
    ) AS rate_early
  FROM currencies_used cu
),
rate_2025 AS (
  SELECT cu.currency_code AS currency_code,
    (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2025-12-31') AS rate_late
  FROM currencies_used cu
)
SELECT
  cu.currency_code AS currency_code,
  COALESCE(tc.tenant_count,0) AS tenant_count,
  COALESCE(o25.orders_2025_total_eur,0) AS orders_2025_total_eur,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r23.rate_early END AS rate_eur_to_currency_2023_01_02,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r25.rate_late END AS rate_eur_to_currency_2025_12_31,
  CASE WHEN cu.currency_code='EUR' THEN 0
  ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100
  END AS pct_rate_change
FROM currencies_used cu
LEFT JOIN tenant_counts tc ON tc.currency_code = cu.currency_code
LEFT JOIN orders_2025 o25 ON o25.currency_code = cu.currency_code
LEFT JOIN rate_2023 r23 ON r23.currency_code = cu.currency_code
LEFT JOIN rate_2025 r25 ON r25.currency_code = cu.currency_code
ORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_code NULLS LAST

WITH currencies_used AS (
  SELECT DISTINCT currency_code FROM orders
),
tenant_counts AS (
  SELECT default_currency_code AS currency_code, COUNT(DISTINCT id) AS tenant_count
  FROM tenants
  GROUP BY default_currency_code
),
orders_2025 AS (
  SELECT o.currency_code AS currency_code,
    SUM(
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END
    ) AS orders_2025_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
  GROUP BY o.currency_code
),
rate_2023 AS (
  SELECT cu.currency_code AS currency_code,
    COALESCE(
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2023-01-02'),
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC NULLS LAST LIMIT 1)
    ) AS rate_early
  FROM currencies_used cu
),
rate_2025 AS (
  SELECT cu.currency_code AS currency_code,
    (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2025-12-31') AS rate_late
  FROM currencies_used cu
)
SELECT
  cu.currency_code AS currency_code,
  COALESCE(tc.tenant_count,0) AS tenant_count,
  COALESCE(o25.orders_2025_total_eur,0) AS orders_2025_total_eur,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r23.rate_early END AS rate_eur_to_currency_2023_01_02,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r25.rate_late END AS rate_eur_to_currency_2025_12_31,
  CASE WHEN cu.currency_code='EUR' THEN 0
  ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100
  END AS pct_rate_change
FROM currencies_used cu
LEFT JOIN tenant_counts tc ON tc.currency_code = cu.currency_code
LEFT JOIN orders_2025 o25 ON o25.currency_code = cu.currency_code
LEFT JOIN rate_2023 r23 ON r23.currency_code = cu.currency_code
LEFT JOIN rate_2025 r25 ON r25.currency_code = cu.currency_code
ORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_code NULLS LAST

Firebolt{ "errors": [ { "description": "Line 13, Column 36: 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": "5114e056-d6f1-426e-9bb2-f767495c412b", "query_lab
DataFusionError during planning: Correlated scalar subquery must be aggregated to return at most one row
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table6.grand_total Decimal(14, 4) Decimal64(size = 0), __table7.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. q098 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

Omar's whole session, 7 turns

The request, as Omar received it

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

Title: EUR exchange rate movement by currency, 2023 vs 2025
Question: How much have the currencies our merchants transact in moved against EUR over the three years of history, and is that movement large enough to distort revenue trends when we roll up multi-currency figures over time?
Grain: currency code
Columns:
- currency_code
- tenant_count
- orders_2025_total_eur
- rate_eur_to_currency_2023_01_02
- rate_eur_to_currency_2025_12_31
- pct_rate_change
Details: Scope: every currency_code that appears in orders.currency_code across the full history. tenant_count is the count of distinct tenants whose default_currency_code equals this currency. orders_2025_total_eur is the sum of orders.grand_total for non-cancelled orders placed in 2025, converted to EUR via exchange_rates using the order's currency_code and placed_at date. rate_eur_to_currency_2023_01_02 is exchange_rates.rate where base_code is EUR, quote_code is this currency, and as_of is 2023-01-02, falling back to the earliest available date in January 2023 if that exact date is missing. rate_eur_to_currency_2025_12_31 is the same lookup for as_of 2025-12-31. pct_rate_change is the late rate minus the early rate, divided by the early rate, times 100, rounded to 1 decimal. Tables: orders, tenants, exchange_rates. Order by orders_2025_total_eur descending.

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

Omar calls dry_run

WITH currencies_used AS (
  SELECT DISTINCT currency_code FROM orders
),
tenant_counts AS (
  SELECT default_currency_code AS currency_code, COUNT(DISTINCT id) AS tenant_count
  FROM tenants
  GROUP BY default_currency_code
),
orders_2025 AS (
  SELECT o.currency_code,
    SUM(
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE o.grand_total / er.rate END
    ) AS orders_2025_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
  GROUP BY o.currency_code
),
rate_2023 AS (
  SELECT cu.currency_code,
    COALESCE(
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2023-01-02'),
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC LIMIT 1)
    ) AS rate_early
  FROM currencies_used cu
),
rate_2025 AS (
  SELECT cu.currency_code,
    (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2025-12-31') AS rate_late
  FROM currencies_used cu
)
SELECT
  cu.currency_code,
  COALESCE(tc.tenant_count,0) AS tenant_count,
  ROUND(COALESCE(o25.orders_2025_total_eur,0),2) AS orders_2025_total_eur,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r23.rate_early END AS rate_eur_to_currency_2023_01_02,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r25.rate_late END AS rate_eur_to_currency_2025_12_31,
  ROUND(
    CASE WHEN cu.currency_code='EUR' THEN 0
    ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100
    END, 1
  ) AS pct_rate_change
FROM currencies_used cu
LEFT JOIN tenant_counts tc ON tc.currency_code = cu.currency_code
LEFT JOIN orders_2025 o25 ON o25.currency_code = cu.currency_code
LEFT JOIN rate_2023 r23 ON r23.currency_code = cu.currency_code
LEFT JOIN rate_2025 r25 ON r25.currency_code = cu.currency_code
ORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_code

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: currency_code, tenant_count, orders_2025_total_eur, rate_eur_to_currency_2023_01_02, rate_eur_to_currency_2025_12_31, pct_rate_change

OmarQuery compiles correctly with all requested columns. Submitting.

Omar calls submit

WITH currencies_used AS (
  SELECT DISTINCT currency_code FROM orders
),
tenant_counts AS (
  SELECT default_currency_code AS currency_code, COUNT(DISTINCT id) AS tenant_count
  FROM tenants
  GROUP BY default_currency_code
),
orders_2025 AS (
  SELECT o.currency_code,
    SUM(
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE o.grand_total / er.rate END
    ) AS orders_2025_total_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = o.currency_code
   AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
  GROUP BY o.currency_code
),
rate_2023 AS (
  SELECT cu.currency_code,
    COALESCE(
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2023-01-02'),
      (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of >= DATE '2023-01-01' AND er.as_of < DATE '2023-02-01' ORDER BY er.as_of ASC LIMIT 1)
    ) AS rate_early
  FROM currencies_used cu
),
rate_2025 AS (
  SELECT cu.currency_code,
    (SELECT er.rate FROM exchange_rates er WHERE er.base_code='EUR' AND er.quote_code=cu.currency_code AND er.as_of = DATE '2025-12-31') AS rate_late
  FROM currencies_used cu
)
SELECT
  cu.currency_code,
  COALESCE(tc.tenant_count,0) AS tenant_count,
  ROUND(COALESCE(o25.orders_2025_total_eur,0),2) AS orders_2025_total_eur,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r23.rate_early END AS rate_eur_to_currency_2023_01_02,
  CASE WHEN cu.currency_code='EUR' THEN 1.0 ELSE r25.rate_late END AS rate_eur_to_currency_2025_12_31,
  ROUND(
    CASE WHEN cu.currency_code='EUR' THEN 0
    ELSE ( (r25.rate_late - r23.rate_early) / NULLIF(r23.rate_early,0) ) * 100
    END, 1
  ) AS pct_rate_change
FROM currencies_used cu
LEFT JOIN tenant_counts tc ON tc.currency_code = cu.currency_code
LEFT JOIN orders_2025 o25 ON o25.currency_code = cu.currency_code
LEFT JOIN rate_2023 r23 ON r23.currency_code = cu.currency_code
LEFT JOIN rate_2025 r25 ON r25.currency_code = cu.currency_code
ORDER BY orders_2025_total_eur DESC NULLS LAST, cu.currency_code

The warehouse answers

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

OmarSubmitted. The report gives one row per currency_code used in orders, with tenant counts, 2025 EUR-converted order totals, the EUR→currency rates at the start and end of the three-year history, and the percent change between them — ordered by order volume descending.

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 (270 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 (4 KB) · the week's memo (3 KB)

EUR exchange rate movement by currency, 2023 vs 2025

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

Rosa Van den Berg writes to Yuki Tanaka

Yuki Tanaka plans the night

Omar writes the SQL

The report comes back overnight

It becomes benchmark query q098

Everything, as recorded