DB Olympics

Queries · q367 · Head of Pricing and Promotions · week 2, night 4

Reconciled 2025 orphan population by tenant and subscription status (single pass)

“In one single query, does the 2025 orphan subscription-delivery population split by tenant reproduce last week's known-good totals (Grind Collective 167, Honeycomb Larder 127, Kibblewick 100, Feather & Fin 49, Saffron & Sage 17, The Balm Room 1 — 461 orders, €22,318.18 total), and how does each tenant's orphan pool split by the customer's most-recent subscription status?”

Manager · asked the questionMarco BianchiHead of Pricing and Promotions. Previously in revenue management at a travel company.
Analyst · planned the reportOliver GrantPeer-minded: compares each merchant with peers of similar size and vertical.
Engineer · wrote the SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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 Pricing and Promotions's desk

    Week 1; shared with Tobias Lindqvist, Chief Financial Officer

    Finance questions the cost of discounts. Which promotions and coupons brought additional sales, which only gave away margin, and is anything being misused?

    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. Marco Bianchi writes to Oliver Grant

    The manager, at the start of week 2

    Subject: Good work — here's where I want to go next

    Oliver,

    This is exactly the kind of memo I needed — it separates real findings from artifacts, which is rarer than it should be. A few reactions and where I want to push next week.

    The lapsed-subscriber giveaway (~€57k) is now the headline for Tobias, not the promo/coupon story — good catch pulling it out. Before I take that number upstairs, I want it airtight. And the Welcome10 broad refund pattern is the one piece of "margin erosion" I think is genuinely actionable rather than a sample-size illusion, so I want to go a layer deeper there before we talk about capping anything.

    On incrementality — I accept we may never get a clean causal answer here, but I'd like one more honest attempt before I write it off as "probably just bigger carts clear the threshold anyway."

    Questions for this week:

    1. Is the active-but-orphan 18% a timing bug or a real gap? I need to know whether the full €57k-ish exposure is confirmed leakage or whether part of it shrinks once the overlap check is fixed properly.
    2. What's actually driving Welcome10's refund pattern? Is it concentrated in particular products, categories, or merchants, or does it look like people discount-shopping and then returning — i.e., is this a fit/expectation problem or a "discount attracts returners" problem?
    3. Can we get a cleaner incrementality read by comparing similar-sized orders with and without the discount, rather than comparing discounted vs. all orders? I'd rather have a more honest "we still can't tell" than a flattering but confounded number.
    4. For the lapsed-subscriber leakage, how far back does it go and is it accelerating or steady? I want to know if this is a slow-burning historical gap or something that's gotten worse recently, since that changes how urgently we need to fix the underlying process versus just write off the past.

    No need to re-litigate the B2B volume-tier outlier — just keep it excluded as you planned. Let's talk Wednesday before the last batch so I can steer based on what the orphan-subscription check turns up.

    Thanks, Marco

  3. Oliver Grant plans the night

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

    Before writing anything near-final for Tobias, I need to flag something I should have caught earlier: the numbers across this whole investigation don't reconcile with each other. The original per-tenant orphan count for 2025 (night before last, summing Grind Collective 167 + Honeycomb Larder 127 + Kibblewick 100 + Feather & Fin 49 + Saffron & Sage 17 + The Balm Room 1 = 461 orders, €22,318.18 total) is far smaller than the "same population" reported a night later when split by subscription status (1,322 canceled + 291 active = 1,613 orders, €68,914.08 total) — a ~3.5x gap that I didn't catch before sending Friday's memo. And tonight's attempt to classify the 291 "active" orders by date logic came back internally inconsistent on its own terms: it reports 291 orders in one category but a discount total (€3,363.71) that matches a completely different, smaller 88-order subset from two nights ago — the count and the amount don't belong to the same population. Three different engineers independently "rebuilding the population from scratch" each night has clearly introduced drift (likely a 2025-only vs. full-history mismatch, and probably a join fan-out on the amount side).

    I'm treating this as the priority for my last night: I'd rather hand Marco a number I've personally reconciled than compound a questionable one. All three requests tonight are single, self-contained, fan-out-safe queries that explicitly validate against previously-known, trusted reference totals, so I can see exactly where the drift is before writing anything definitive:

    1. A single query, at tenant grain, for 2025 only, that reproduces last week's known-good per-tenant orphan totals (461 orders / €22,318.18) AND splits each tenant by subscription status in the same pass — so population and status-split can't drift apart.
    2. The same construction extended to full history (2023-2025), checked against the monthly trend's cumulative total (~€57.7k for the canceled bucket, ~1,188 orders by my own hand-count of last week's monthly figures).
    3. A careful, fan-out-safe rebuild of the date-logic classification for the active-bucket (started-after-order vs. active-but-already-ended vs. likely bug), built order-by-order first before aggregating, so the category counts and the discount total can't come from different populations.

    I won't write the final headline number until I see these three agree with each other and with the known reference totals.

    Reconciled 2025 orphan population by tenant and subscription status (single pass)
    Question
    In one single query, does the 2025 orphan subscription-delivery population split by tenant reproduce last week's known-good totals (Grind Collective 167, Honeycomb Larder 127, Kibblewick 100, Feather & Fin 49, Saffron & Sage 17, The Balm Room 1 — 461 orders, €22,318.18 total), and how does each tenant's orphan pool split by the customer's most-recent subscription status?
    One row per
    tenant and subscription-status category (canceled / active / other-or-no-subscription-record)
    Columns
    • tenant name
    • status category
    • orphan order count
    • distinct customers
    • total discount amount (EUR)

  4. Ines 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 base_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.placed_at,
    o.currency_code
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status != 'cancelled'
    AND EXISTS (
      SELECT 1
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = o.id
        AND d.name = 'Subscription delivery (prepaid)'
    )
),
row_per_order AS (
  SELECT
    bo.order_id,
    bo.tenant_id,
    bo.user_id,
    bo.placed_at,
    bo.currency_code,
    (
      SELECT SUM(od.amount)
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = bo.order_id
        AND d.name = 'Subscription delivery (prepaid)'
    ) AS discount_amount,
    NOT EXISTS (
      SELECT 1
      FROM subscriptions s
      WHERE s.tenant_id = bo.tenant_id
        AND s.user_id = bo.user_id
        AND s.status IN ('trialing', 'active', 'past_due')
        AND s.started_at <= bo.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= bo.placed_at)
    ) AS is_orphan,
    (
      SELECT s2.status
      FROM subscriptions s2
      WHERE s2.tenant_id = bo.tenant_id
        AND s2.user_id = bo.user_id
      ORDER BY s2.started_at DESC, s2.id DESC
      LIMIT 1
    ) AS most_recent_status,
    (
      SELECT er.rate
      FROM exchange_rates er
      WHERE er.base_code = 'EUR'
        AND er.quote_code = bo.currency_code
        AND er.as_of = CAST(bo.placed_at AS DATE)
    ) AS fx_rate
  FROM base_orders bo
),
categorized AS (
  SELECT
    r.*,
    CASE
      WHEN r.most_recent_status = 'canceled' THEN 'canceled'
      WHEN r.most_recent_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN r.currency_code = 'EUR' THEN r.discount_amount
      ELSE r.discount_amount / NULLIF(r.fx_rate, 0)
    END AS eur_amount
  FROM row_per_order r
)
SELECT
  t.name AS tenant_name,
  c.status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT c.user_id) AS distinct_customers,
  ROUND(SUM(c.eur_amount), 2) AS total_discount_amount_eur
FROM categorized c
JOIN tenants t ON t.id = c.tenant_id
WHERE c.is_orphan
GROUP BY t.name, c.status_category
ORDER BY t.name, c.status_category

    “Query compiles and matches the requested grain and columns.”

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

    “Submitted. Summary of what the query does and the one caveat worth flagging to Oliver: - One pass, per-order grain first: base_orders → row_per_order (scalar subqueries, not joins, for discount sum / orphan flag / most-recent status / FX rate) → categorized (status mapping + EUR conversion) → final GROUP BY tenant + category. - Orphan definition: no subscriptions row for that user/tenant with status in trialing/active/past_due whose started_at–ended_at window covers placed_at. - Status category: most recent subscription (by started_at) mapped to canceled / active / everything else or no record at all → other-or-no-subscription-record. - FX: discount amounts converted to EUR via exchange_rates (EUR→currency_code, as_of = placed_at date), EUR orders pass through unchanged. One limitation noted in the submit note: I can't actually execute this against real data (only free schema-only dry runs), so I couldn't verify tonight's output against last week's reference totals myself — that reconciliation needs to happen once the batch prints the report.”

  5. The report comes back overnight

    As Oliver Grant saw it, from the small dataset the agents worked on

    tenant_name           | status_category | orphan_order_count | distinct_customers | total_discount_amount_eur
----------------------+-----------------+--------------------+--------------------+--------------------------
Brennquell Kaffee     | active          |                165 |                 35 |                   6644.97
Brennquell Kaffee     | canceled        |                726 |                127 |                   30086.8
Chocolaterie Mireille | canceled        |                 14 |                  2 |                     74.22
Feather & Fin         | active          |                 10 |                  3 |                    745.13
Feather & Fin         | canceled        |                 39 |                  7 |                    1051.5
Grind Collective      | active          |                 41 |                  8 |                   1094.27
Grind Collective      | canceled        |                126 |                 20 |                   8213.85
Honeycomb Larder      | active          |                 24 |                  5 |                    933.19
Honeycomb Larder      | canceled        |                103 |                 19 |                   5045.72
Kibblewick            | active          |                 13 |                  3 |                    591.12
Kibblewick            | canceled        |                 87 |                 18 |                    2489.6
Mirelle               | canceled        |                 14 |                  3 |                   1365.36
Pfotenglück           | canceled        |                 18 |                  4 |                    609.38
Saffron & Sage        | canceled        |                 17 |                  5 |                   1974.96
Spice Route Box       | active          |                 33 |                  5 |                    1501.9
Spice Route Box       | canceled        |                172 |                 30 |                   5924.43
The Balm Room         | canceled        |                  1 |                  1 |                    178.84
Whisker Works         | active          |                  5 |                  1 |                    235.81
Whisker Works         | canceled        |                  5 |                  2 |                    153.03
(19 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:

    It builds on:

  7. It becomes benchmark query q367

    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: x / exchange rate cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (10×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    WITH base_orders AS (  SELECT    o.id AS order_id,    o.tenant_id,    o.user_id,    o.placed_at,    o.currency_code    o.tenant_id AS tenant_id,    o.user_id AS user_id,    o.placed_at AS placed_at,    o.currency_code AS currency_code  FROM orders o  WHERE o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status != 'cancelled'    AND EXISTS (      SELECT 1      FROM order_discounts od      JOIN discounts d ON d.id = od.discount_id      WHERE od.order_id = o.id        AND d.name = 'Subscription delivery (prepaid)'    )),row_per_order AS (  SELECT    bo.order_id,    bo.tenant_id,    bo.user_id,    bo.placed_at,    bo.currency_code,    bo.order_id AS order_id,    bo.tenant_id AS tenant_id,    bo.user_id AS user_id,    bo.placed_at AS placed_at,    bo.currency_code AS currency_code,    (      SELECT SUM(od.amount)      FROM order_discounts od      JOIN discounts d ON d.id = od.discount_id      WHERE od.order_id = bo.order_id        AND d.name = 'Subscription delivery (prepaid)'    ) AS discount_amount,    NOT EXISTS (      SELECT 1      FROM subscriptions s      WHERE s.tenant_id = bo.tenant_id        AND s.user_id = bo.user_id        AND s.status IN ('trialing', 'active', 'past_due')        AND s.started_at <= bo.placed_at        AND (s.ended_at IS NULL OR s.ended_at >= bo.placed_at)    ) AS is_orphan,    (      SELECT s2.status      FROM subscriptions s2      WHERE s2.tenant_id = bo.tenant_id        AND s2.user_id = bo.user_id      ORDER BY s2.started_at DESC, s2.id DESC      LIMIT 1      ORDER BY s2.started_at DESC NULLS FIRST, s2.id DESC NULLS FIRST      {{fetch_first(1)}}    ) AS most_recent_status,    (      SELECT er.rate      FROM exchange_rates er      WHERE er.base_code = 'EUR'        AND er.quote_code = bo.currency_code        AND er.as_of = CAST(bo.placed_at AS DATE)    ) AS fx_rate  FROM base_orders bo),categorized AS (  SELECT    r.*,    CASE      WHEN r.most_recent_status = 'canceled' THEN 'canceled'      WHEN r.most_recent_status = 'active' THEN 'active'      ELSE 'other-or-no-subscription-record'    END AS status_category,    CASE      WHEN r.currency_code = 'EUR' THEN r.discount_amount      ELSE r.discount_amount / NULLIF(r.fx_rate, 0)      ELSE CAST(r.discount_amount / NULLIF(r.fx_rate, 0) AS NUMERIC(18,6))    END AS eur_amount  FROM row_per_order r)SELECT  t.name AS tenant_name,  c.status_category,  c.status_category AS status_category,  COUNT(*) AS orphan_order_count,  COUNT(DISTINCT c.user_id) AS distinct_customers,  ROUND(SUM(c.eur_amount), 2) AS total_discount_amount_eur  SUM(c.eur_amount) AS total_discount_amount_eurFROM categorized cJOIN tenants t ON t.id = c.tenant_idWHERE c.is_orphanGROUP BY t.name, c.status_categoryORDER BY t.name, c.status_categoryORDER BY t.name NULLS LAST, c.status_category NULLS LAST

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

    WITH base_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id AS tenant_id,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    o.currency_code AS currency_code
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status != 'cancelled'
    AND EXISTS (
      SELECT 1
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = o.id
        AND d.name = 'Subscription delivery (prepaid)'
    )
),
row_per_order AS (
  SELECT
    bo.order_id AS order_id,
    bo.tenant_id AS tenant_id,
    bo.user_id AS user_id,
    bo.placed_at AS placed_at,
    bo.currency_code AS currency_code,
    (
      SELECT SUM(od.amount)
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = bo.order_id
        AND d.name = 'Subscription delivery (prepaid)'
    ) AS discount_amount,
    NOT EXISTS (
      SELECT 1
      FROM subscriptions s
      WHERE s.tenant_id = bo.tenant_id
        AND s.user_id = bo.user_id
        AND s.status IN ('trialing', 'active', 'past_due')
        AND s.started_at <= bo.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= bo.placed_at)
    ) AS is_orphan,
    (
      SELECT s2.status
      FROM subscriptions s2
      WHERE s2.tenant_id = bo.tenant_id
        AND s2.user_id = bo.user_id
      ORDER BY s2.started_at DESC NULLS FIRST, s2.id DESC NULLS FIRST
      FETCH FIRST 1 ROWS ONLY
    ) AS most_recent_status,
    (
      SELECT er.rate
      FROM exchange_rates er
      WHERE er.base_code = 'EUR'
        AND er.quote_code = bo.currency_code
        AND er.as_of = CAST(bo.placed_at AS DATE)
    ) AS fx_rate
  FROM base_orders bo
),
categorized AS (
  SELECT
    r.*,
    CASE
      WHEN r.most_recent_status = 'canceled' THEN 'canceled'
      WHEN r.most_recent_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN r.currency_code = 'EUR' THEN r.discount_amount
      ELSE CAST(r.discount_amount / NULLIF(r.fx_rate, 0) AS NUMERIC(18,6))
    END AS eur_amount
  FROM row_per_order r
)
SELECT
  t.name AS tenant_name,
  c.status_category AS status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT c.user_id) AS distinct_customers,
  SUM(c.eur_amount) AS total_discount_amount_eur
FROM categorized c
JOIN tenants t ON t.id = c.tenant_id
WHERE c.is_orphan
GROUP BY t.name, c.status_category
ORDER BY t.name NULLS LAST, c.status_category NULLS LAST
    100 ms1 s10 sHyper2.04 sDuckDB406 msGizmoSQL388 msclickhouse_brew1.18 sFirebolterrorDataFusionerrorClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 70, Column 35: 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": "bcff3d1a-13d9-49b1-b3be-8b9138bd9e8b", "query_lab
    • DataFusionError during planning: Correlated scalar subquery must be aggregated to return at most one row
    • ClickHouseCode: 48. DB::Exception: Cannot decorrelate query, because 'Limit' step is not supported. (NOT_IMPLEMENTED)

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

    Ines's whole session, 7 turns
    • The request, as Ines received it
      Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):

Title: Reconciled 2025 orphan population by tenant and subscription status (single pass)
Question: In one single query, does the 2025 orphan subscription-delivery population split by tenant reproduce last week's known-good totals (Grind Collective 167, Honeycomb Larder 127, Kibblewick 100, Feather & Fin 49, Saffron & Sage 17, The Balm Room 1 — 461 orders, €22,318.18 total), and how does each tenant's orphan pool split by the customer's most-recent subscription status?
Grain: one row per tenant and subscription-status category (canceled / active / other-or-no-subscription-record)
Columns:
- tenant name
- status category
- orphan order count
- distinct customers
- total discount amount (EUR)
Details: Source: order_discounts + discounts (name='Subscription delivery (prepaid)') + orders (placed_at 2025-01-01 to 2025-12-31, status != 'cancelled', user_id, tenant_id) + tenants. Build this as ONE query: first produce a single row per order_id with its orphan flag (no overlapping subscription with status in trialing/active/past_due whose started_at/ended_at window covers placed_at) and its most-recent-subscription status (by started_at, any status, or 'no_subscription_record'), using subqueries/window functions rather than joins that could duplicate rows; only then aggregate by tenant and status. Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). As a validation step, please confirm in the engineer's note whether each tenant's total orphan order count (summed across status categories) matches the reference figures above, and flag explicitly if not along with a hypothesis why. Order by tenant name, then status category. Print all rows (expect ~6 tenants x up to 3 categories).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      WITH base_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.placed_at,
    o.currency_code
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status != 'cancelled'
    AND EXISTS (
      SELECT 1
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = o.id
        AND d.name = 'Subscription delivery (prepaid)'
    )
),
row_per_order AS (
  SELECT
    bo.order_id,
    bo.tenant_id,
    bo.user_id,
    bo.placed_at,
    bo.currency_code,
    (
      SELECT SUM(od.amount)
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = bo.order_id
        AND d.name = 'Subscription delivery (prepaid)'
    ) AS discount_amount,
    NOT EXISTS (
      SELECT 1
      FROM subscriptions s
      WHERE s.tenant_id = bo.tenant_id
        AND s.user_id = bo.user_id
        AND s.status IN ('trialing', 'active', 'past_due')
        AND s.started_at <= bo.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= bo.placed_at)
    ) AS is_orphan,
    (
      SELECT s2.status
      FROM subscriptions s2
      WHERE s2.tenant_id = bo.tenant_id
        AND s2.user_id = bo.user_id
      ORDER BY s2.started_at DESC, s2.id DESC
      LIMIT 1
    ) AS most_recent_status,
    (
      SELECT er.rate
      FROM exchange_rates er
      WHERE er.base_code = 'EUR'
        AND er.quote_code = bo.currency_code
        AND er.as_of = CAST(bo.placed_at AS DATE)
    ) AS fx_rate
  FROM base_orders bo
),
categorized AS (
  SELECT
    r.*,
    CASE
      WHEN r.most_recent_status = 'canceled' THEN 'canceled'
      WHEN r.most_recent_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN r.currency_code = 'EUR' THEN r.discount_amount
      ELSE r.discount_amount / NULLIF(r.fx_rate, 0)
    END AS eur_amount
  FROM row_per_order r
)
SELECT
  t.name AS tenant_name,
  c.status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT c.user_id) AS distinct_customers,
  ROUND(SUM(c.eur_amount), 2) AS total_discount_amount_eur
FROM categorized c
JOIN tenants t ON t.id = c.tenant_id
WHERE c.is_orphan
GROUP BY t.name, c.status_category
ORDER BY t.name, c.status_category
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, status_category, orphan_order_count, distinct_customers, total_discount_amount_eur
    • InesQuery compiles and matches the requested grain and columns.
    • Ines calls submit
      WITH base_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.placed_at,
    o.currency_code
  FROM orders o
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status != 'cancelled'
    AND EXISTS (
      SELECT 1
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = o.id
        AND d.name = 'Subscription delivery (prepaid)'
    )
),
row_per_order AS (
  SELECT
    bo.order_id,
    bo.tenant_id,
    bo.user_id,
    bo.placed_at,
    bo.currency_code,
    (
      SELECT SUM(od.amount)
      FROM order_discounts od
      JOIN discounts d ON d.id = od.discount_id
      WHERE od.order_id = bo.order_id
        AND d.name = 'Subscription delivery (prepaid)'
    ) AS discount_amount,
    NOT EXISTS (
      SELECT 1
      FROM subscriptions s
      WHERE s.tenant_id = bo.tenant_id
        AND s.user_id = bo.user_id
        AND s.status IN ('trialing', 'active', 'past_due')
        AND s.started_at <= bo.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= bo.placed_at)
    ) AS is_orphan,
    (
      SELECT s2.status
      FROM subscriptions s2
      WHERE s2.tenant_id = bo.tenant_id
        AND s2.user_id = bo.user_id
      ORDER BY s2.started_at DESC, s2.id DESC
      LIMIT 1
    ) AS most_recent_status,
    (
      SELECT er.rate
      FROM exchange_rates er
      WHERE er.base_code = 'EUR'
        AND er.quote_code = bo.currency_code
        AND er.as_of = CAST(bo.placed_at AS DATE)
    ) AS fx_rate
  FROM base_orders bo
),
categorized AS (
  SELECT
    r.*,
    CASE
      WHEN r.most_recent_status = 'canceled' THEN 'canceled'
      WHEN r.most_recent_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN r.currency_code = 'EUR' THEN r.discount_amount
      ELSE r.discount_amount / NULLIF(r.fx_rate, 0)
    END AS eur_amount
  FROM row_per_order r
)
SELECT
  t.name AS tenant_name,
  c.status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT c.user_id) AS distinct_customers,
  ROUND(SUM(c.eur_amount), 2) AS total_discount_amount_eur
FROM categorized c
JOIN tenants t ON t.id = c.tenant_id
WHERE c.is_orphan
GROUP BY t.name, c.status_category
ORDER BY t.name, c.status_category
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. Summary of what the query does and the one caveat worth flagging to Oliver: - **One pass, per-order grain first:** `base_orders` → `row_per_order` (scalar subqueries, not joins, for discount sum / orphan flag / most-recent status / FX rate) → `categorized` (status mapping + EUR conversion) → final `GROUP BY` tenant + category. - **Orphan definition:** no `subscriptions` row for that user/tenant with status in `trialing/active/past_due` whose `started_at`–`ended_at` window covers `placed_at`. - **Status category:** most recent subscription (by `started_at`) mapped to `canceled` / `active` / everything else or no record at all → `other-or-no-subscription-record`. - **FX:** discount amounts converted to EUR via `exchange_rates` (EUR→currency_code, `as_of = placed_at` date), EUR orders pass through unchanged. One limitation noted in the submit note: I can't actually execute this against real data (only free schema-only dry runs), so I couldn't verify tonight's output against last week's reference totals myself — that reconciliation needs to happen once the batch prints the report.

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

    Ines's transcript (286 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (8 KB) · the night's reports (7 KB) · the week's memo (3 KB)