Queries · q092 · Head of Data · week 1, night 2
“Is invoice revenue additional revenue on top of what we already count in orders, or does some of it double-count order revenue we've already booked?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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?
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:
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
Subject: What I need for the revenue/payout certification
Hi Yuki,
A few things from my side as you scope this week's work:
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
The analyst · week 1, night 2, request 2 of 3
Night 1 surfaced three things worth running down before I widen scope further: (1) orders_booked_revenue is consistently 15-40% above captured payments, and critically this gap does NOT shrink for old cohorts (e.g. 2023-04 orders are 2.5 years old and still show a 39% gap, 2025-01 orders are a year old and still show 15.5%) — that rules out simple timing lag and points to a structural cause (orders that never convert to a captured payment, e.g. abandoned/declined/cancelled), which matters a lot for "revenue" certification if orders.grand_total is ever quoted as revenue. I want to confirm this is explained by order status rather than something stranger. (2) invoice_paid_revenue is much smaller than order revenue and I'm not yet sure whether it's incremental (subscription billing on top of orders) or overlapping (already counted inside order revenue) — that changes whether we're under- or double-counting. (3) Report 3's net_check_diff_eur (net vs gross-fee) is large and growing, but shrinks a lot once refunds are subtracted too, suggesting the true formula is net ≈ gross - fee - refunds and refunds aren't double-counted — but I matched refunds by calendar month only, which is a loose proxy and leaves real noise (-6k to +21k swings). I want the structural, payment-linked version of that reconciliation via payout_items to see if the noise disappears, which would let us certify the payout formula with confidence rather than a caveat.
The engineer · 2 dry runs on the empty schema, then the submission
SELECT to_char(date_trunc('month', i.paid_at), 'YYYY-MM') as month, round(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 2) as invoice_paid_total_eur, round(sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_order_linked_eur, round(sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_subscription_only_eur, round(sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_other_eur, round(100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 0), 1) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = date(i.paid_at) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at)
SELECT to_char(date_trunc('month', i.paid_at), 'YYYY-MM') as month, strftime(date_trunc('month', i.paid_at), '%Y-%m') as month, round(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 2) as invoice_paid_total_eur, round(sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_order_linked_eur, round(sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_subscription_only_eur, round(sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_other_eur, round(100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 0), 1) as pct_order_linkedFROM invoices iLEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = date(i.paid_at)WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01'GROUP BY date_trunc('month', i.paid_at)ORDER BY date_trunc('month', i.paid_at)
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report groups paid invoices by paid_at month, converts each invoice's total to EUR (direct pass-through for EUR, divide by the EUR→currency rate otherwise), and splits the EUR total into order-linked, subscription-only, and other buckets with the order-linked share as a percentage — exactly as Yuki specified.”
As Yuki Tanaka saw it, from the small dataset the agents worked on
month | invoice_paid_total_eur | invoice_order_linked_eur | invoice_subscription_only_eur | invoice_other_eur | pct_order_linked --------+------------------------+--------------------------+-------------------------------+-------------------+----------------- 2023-01 | 1208.67 | 0.0 | 1208.67 | 0.0 | 0.0 2023-02 | 57088.25 | 55586.86 | 1501.39 | 0.0 | 97.4 2023-03 | 92309.45 | 90098.34 | 2211.11 | 0.0 | 97.6 2023-04 | 87161.08 | 84076.61 | 3084.47 | 0.0 | 96.5 2023-05 | 107059.07 | 103953.39 | 3105.68 | 0.0 | 97.1 2023-06 | 125326.49 | 121785.3 | 3541.19 | 0.0 | 97.2 2023-07 | 78309.79 | 74767.17 | 3542.62 | 0.0 | 95.5 2023-08 | 95262.6 | 91291.37 | 3971.23 | 0.0 | 95.8 2023-09 | 71634.92 | 67326.75 | 4308.17 | 0.0 | 94.0 2023-10 | 81115.25 | 75548.86 | 5566.4 | 0.0 | 93.1 2023-11 | 119071.89 | 114019.55 | 5052.34 | 0.0 | 95.8 2023-12 | 106958.11 | 100806.09 | 6152.02 | 0.0 | 94.2 2024-01 | 90433.93 | 84877.57 | 5556.36 | 0.0 | 93.9 2024-02 | 101897.25 | 95804.11 | 6093.14 | 0.0 | 94.0 2024-03 | 116331.35 | 109527.5 | 6803.86 | 0.0 | 94.2 2024-04 | 83682.37 | 75268.16 | 8414.21 | 0.0 | 89.9 2024-05 | 140285.73 | 133408.8 | 6876.94 | 0.0 | 95.1 2024-06 | 146918.11 | 140129.78 | 6788.33 | 0.0 | 95.4 2024-07 | 155621.07 | 147339.35 | 8281.72 | 0.0 | 94.7 2024-08 | 157117.05 | 149446.85 | 7670.21 | 0.0 | 95.1 2024-09 | 149250.08 | 141244.75 | 8005.33 | 0.0 | 94.6 2024-10 | 133982.17 | 125708.78 | 8273.39 | 0.0 | 93.8 2024-11 | 148581.38 | 140597.13 | 7984.26 | 0.0 | 94.6 2024-12 | 174971.7 | 164551.7 | 10420.0 | 0.0 | 94.0 2025-01 | 131878.61 | 122947.74 | 8930.88 | 0.0 | 93.2 2025-02 | 174177.04 | 164555.01 | 9622.02 | 0.0 | 94.5 2025-03 | 196178.01 | 185889.65 | 10288.36 | 0.0 | 94.8 2025-04 | 197968.6 | 187140.39 | 10828.22 | 0.0 | 94.5 2025-05 | 154986.16 | 145026.16 | 9960.0 | 0.0 | 93.6 2025-06 | 170275.21 | 159622.58 | 10652.64 | 0.0 | 93.7 2025-07 | 139537.62 | 129789.15 | 9748.48 | 0.0 | 93.0 2025-08 | 135141.56 | 124271.46 | 10870.11 | 0.0 | 92.0 2025-09 | 151817.96 | 141333.26 | 10484.71 | 0.0 | 93.1 2025-10 | 170607.25 | 159984.18 | 10623.06 | 0.0 | 93.8 2025-11 | 213438.12 | 201408.36 | 12029.77 | 0.0 | 94.4 2025-12 | 246816.09 | 231688.26 | 15127.83 | 0.0 | 93.9 (36 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT strftime(date_trunc('month', i.paid_at), '%Y-%m') as month, round(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 2) as invoice_paid_total_eur, round(sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_order_linked_eur, round(sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_subscription_only_eur, round(sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_other_eur, round(100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 0), 1) as pct_order_linked {{year_month(date_trunc('month', i.paid_at))}} as month, sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) as invoice_paid_total_eur, sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_order_linked_eur, sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_subscription_only_eur, sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_other_eur, 100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end), 0) as pct_order_linkedFROM invoices iLEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = date(i.paid_at) AND er.as_of = CAST(i.paid_at AS DATE)WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01'GROUP BY date_trunc('month', i.paid_at)ORDER BY date_trunc('month', i.paid_at)ORDER BY date_trunc('month', i.paid_at) NULLS LAST
SELECT strftime(date_trunc('month', i.paid_at), '%Y-%m') as month, sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) as invoice_paid_total_eur, sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_order_linked_eur, sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_subscription_only_eur, sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_other_eur, 100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end), 0) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at) NULLS LAST
SELECT to_char(date_trunc('month', i.paid_at), 'YYYY-MM') as month, sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) as invoice_paid_total_eur, sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_order_linked_eur, sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_subscription_only_eur, sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_other_eur, 100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end), 0) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at) NULLS LAST
SELECT to_char(CAST(date_trunc('month', i.paid_at) AS timestamp), 'YYYY-MM') as month, sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) as invoice_paid_total_eur, sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_order_linked_eur, sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_subscription_only_eur, sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_other_eur, 100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end), 0) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at) NULLS LAST
SELECT formatDateTime(date_trunc('month', i.paid_at), '%Y-%m') as month, sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) as invoice_paid_total_eur, sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_order_linked_eur, sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_subscription_only_eur, sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) as invoice_other_eur, 100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else CAST(i.total / er.rate AS NUMERIC(18,6)) end), 0) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = CAST(i.paid_at AS DATE) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at) NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Yuki Tanaka (analyst for the Head of Data): Title: Invoice revenue: order-linked vs subscription-only Question: Is invoice revenue additional revenue on top of what we already count in orders, or does some of it double-count order revenue we've already booked? Grain: calendar month, 2023-01 through 2025-12, based on invoice paid_at month Columns: - month - invoice_paid_total_eur - invoice_order_linked_eur - invoice_subscription_only_eur - invoice_other_eur - pct_order_linked Details: Population: invoices with status='paid', grouped by month of paid_at. invoice_paid_total_eur = sum of invoices.total, converted to EUR via exchange_rates (base_code='EUR', rate matched to paid_at date and invoices.currency_code). invoice_order_linked_eur = sum of invoices.total where invoices.order_id is not null (these invoices are tied directly to an order and may already be reflected in orders.grand_total). invoice_subscription_only_eur = sum of invoices.total where invoices.order_id is null and invoices.subscription_id is not null (recurring billing with no linked order - likely incremental revenue not in the orders table at all). invoice_other_eur = sum of invoices.total where both order_id and subscription_id are null. pct_order_linked = invoice_order_linked_eur / invoice_paid_total_eur * 100, rounded to 1 decimal. Tables: invoices, exchange_rates. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT to_char(date_trunc('month', i.paid_at), 'YYYY-MM') as month, round(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 2) as invoice_paid_total_eur, round(sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_order_linked_eur, round(sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_subscription_only_eur, round(sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_other_eur, round(100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 0), 1) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = date(i.paid_at) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at)
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runSELECT strftime(date_trunc('month', i.paid_at), '%Y-%m') as month, round(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 2) as invoice_paid_total_eur, round(sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_order_linked_eur, round(sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_subscription_only_eur, round(sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_other_eur, round(100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 0), 1) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = date(i.paid_at) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at)
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, invoice_paid_total_eur, invoice_order_linked_eur, invoice_subscription_only_eur, invoice_other_eur, pct_order_linked
submitSELECT strftime(date_trunc('month', i.paid_at), '%Y-%m') as month, round(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 2) as invoice_paid_total_eur, round(sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_order_linked_eur, round(sum(case when i.order_id is null and i.subscription_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_subscription_only_eur, round(sum(case when i.order_id is null and i.subscription_id is null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end), 2) as invoice_other_eur, round(100.0 * sum(case when i.order_id is not null then (case when i.currency_code = 'EUR' then i.total else i.total / er.rate end) else 0 end) / nullif(sum(case when i.currency_code = 'EUR' then i.total else i.total / er.rate end), 0), 1) as pct_order_linked FROM invoices i LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = i.currency_code AND er.as_of = date(i.paid_at) WHERE i.status = 'paid' AND i.paid_at >= date '2023-01-01' AND i.paid_at < date '2026-01-01' GROUP BY date_trunc('month', i.paid_at) ORDER BY date_trunc('month', i.paid_at)
Submitted to tonight's batch. Your work on this request is done.