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

Reconciled full-history (2023-2025) orphan population by tenant and subscription status (single pass)

“Using the same single-pass, fan-out-safe construction, does the full-history orphan population by tenant and status reproduce the monthly trend's cumulative canceled-bucket total (~€57,658.66, roughly 1,188 orders by hand-summing last week's monthly report)?”

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 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 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
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
Oliver Grant plans the night
The analyst · week 2, night 4, request 2 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 full-history (2023-2025) orphan population by tenant and subscription status (single pass)
Question
Using the same single-pass, fan-out-safe construction, does the full-history orphan population by tenant and status reproduce the monthly trend's cumulative canceled-bucket total (~€57,658.66, roughly 1,188 orders by hand-summing last week's monthly report)?
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)

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 order_sub AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.discount_total,
    o.currency_code,
    o.placed_at,
    (
      SELECT s.status
      FROM invoices i
      JOIN subscriptions s ON s.id = i.subscription_id
      WHERE i.order_id = o.id
      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC
      LIMIT 1
    ) AS subscription_status
  FROM orders o
  WHERE o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.discount_total > 0
    AND NOT EXISTS (
      SELECT 1 FROM order_discounts od WHERE od.order_id = o.id
    )
),
rated AS (
  SELECT
    os.tenant_id,
    os.user_id,
    os.order_id,
    CASE
      WHEN os.subscription_status = 'canceled' THEN 'canceled'
      WHEN os.subscription_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN os.currency_code = 'EUR' THEN os.discount_total
      ELSE os.discount_total / er.rate
    END AS discount_eur
  FROM order_sub os
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = os.currency_code
    AND er.as_of = os.placed_at::date
)
SELECT
  t.name AS tenant_name,
  r.status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT r.user_id) AS distinct_customers,
  ROUND(SUM(r.discount_eur), 2) AS total_discount_eur
FROM rated r
JOIN tenants t ON t.id = r.tenant_id
GROUP BY t.name, r.status_category
ORDER BY t.name, r.status_category

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 I did and why: - Built the orphan set in a single non-fan-out pass: a per-order subquery flags orders (2023‑01‑01 to 2025‑12‑31, status <> 'cancelled') that have discount_total > 0 but no corresponding order_discounts row (NOT EXISTS), which is the fan-out-safe way to do this since order_discounts can have multiple rows per order. - Attached each orphan order to the status of its most recent linked subscription (via invoices.subscription_id) using a scalar subquery (ORDER BY issued_at DESC LIMIT 1) — again avoiding any join fan-out — then bucketed into canceled / active / other-or-no-subscription-record. - Converted discount_total to EUR via exchange_rates (base EUR, quote = order currency, as_of = placed_at date), treating EUR orders as already in EUR. - Aggregated to tenant × status_category with order count, distinct customers, and EUR discount total, ordered by tenant then category. Flagged two caveats in the note: I had to infer the "orphan" definition since the earlier 2025-only request wasn't visible to me in this session, and as the engineer I never see query output (dry_run uses an empty schema), so I can't personally verify the ~1,188 orders / ~€57,658.66 reconciliation — that comparison needs to happen once the analyst has the printed report.”

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_eur
------------+-----------------+--------------------+--------------------+-------------------
(0 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q360Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025
- q367Reconciled 2025 orphan population by tenant and subscription status (single pass)

It becomes benchmark query q368

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

spellingcast
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 (9×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
placeholderfetch first

The benchmark text, against the submitted SQL

WITH order_sub AS (  SELECT    o.id AS order_id,    o.tenant_id,    o.user_id,    o.discount_total,    o.currency_code,    o.placed_at,    o.tenant_id AS tenant_id,    o.user_id AS user_id,    o.discount_total AS discount_total,    o.currency_code AS currency_code,    o.placed_at AS placed_at,    (      SELECT s.status      FROM invoices i      JOIN subscriptions s ON s.id = i.subscription_id      WHERE i.order_id = o.id      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC      LIMIT 1      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC NULLS FIRST      {{fetch_first(1)}}    ) AS subscription_status  FROM orders o  WHERE o.placed_at >= DATE '2023-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'    AND o.discount_total > 0    AND NOT EXISTS (      SELECT 1 FROM order_discounts od WHERE od.order_id = o.id    )),rated AS (  SELECT    os.tenant_id,    os.user_id,    os.order_id,    os.tenant_id AS tenant_id,    os.user_id AS user_id,    os.order_id AS order_id,    CASE      WHEN os.subscription_status = 'canceled' THEN 'canceled'      WHEN os.subscription_status = 'active' THEN 'active'      ELSE 'other-or-no-subscription-record'    END AS status_category,    CASE      WHEN os.currency_code = 'EUR' THEN os.discount_total      ELSE os.discount_total / er.rate      ELSE CAST(os.discount_total / er.rate AS NUMERIC(18,6))    END AS discount_eur  FROM order_sub os  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = os.currency_code    AND er.as_of = os.placed_at::date    AND er.as_of = CAST(os.placed_at AS date))SELECT  t.name AS tenant_name,  r.status_category,  r.status_category AS status_category,  COUNT(*) AS orphan_order_count,  COUNT(DISTINCT r.user_id) AS distinct_customers,  ROUND(SUM(r.discount_eur), 2) AS total_discount_eur  SUM(r.discount_eur) AS total_discount_eurFROM rated rJOIN tenants t ON t.id = r.tenant_idGROUP BY t.name, r.status_categoryORDER BY t.name, r.status_categoryORDER BY t.name NULLS LAST, r.status_category NULLS LAST

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

WITH order_sub AS (
  SELECT
    o.id AS order_id,
    o.tenant_id AS tenant_id,
    o.user_id AS user_id,
    o.discount_total AS discount_total,
    o.currency_code AS currency_code,
    o.placed_at AS placed_at,
    (
      SELECT s.status
      FROM invoices i
      JOIN subscriptions s ON s.id = i.subscription_id
      WHERE i.order_id = o.id
      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC NULLS FIRST
      FETCH FIRST 1 ROWS ONLY
    ) AS subscription_status
  FROM orders o
  WHERE o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.discount_total > 0
    AND NOT EXISTS (
      SELECT 1 FROM order_discounts od WHERE od.order_id = o.id
    )
),
rated AS (
  SELECT
    os.tenant_id AS tenant_id,
    os.user_id AS user_id,
    os.order_id AS order_id,
    CASE
      WHEN os.subscription_status = 'canceled' THEN 'canceled'
      WHEN os.subscription_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN os.currency_code = 'EUR' THEN os.discount_total
      ELSE CAST(os.discount_total / er.rate AS NUMERIC(18,6))
    END AS discount_eur
  FROM order_sub os
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = os.currency_code
    AND er.as_of = CAST(os.placed_at AS date)
)
SELECT
  t.name AS tenant_name,
  r.status_category AS status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT r.user_id) AS distinct_customers,
  SUM(r.discount_eur) AS total_discount_eur
FROM rated r
JOIN tenants t ON t.id = r.tenant_id
GROUP BY t.name, r.status_category
ORDER BY t.name NULLS LAST, r.status_category NULLS LAST

WITH order_sub AS (
  SELECT
    o.id AS order_id,
    o.tenant_id AS tenant_id,
    o.user_id AS user_id,
    o.discount_total AS discount_total,
    o.currency_code AS currency_code,
    o.placed_at AS placed_at,
    (
      SELECT s.status
      FROM invoices i
      JOIN subscriptions s ON s.id = i.subscription_id
      WHERE i.order_id = o.id
      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC NULLS FIRST
      LIMIT 1
    ) AS subscription_status
  FROM orders o
  WHERE o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.discount_total > 0
    AND NOT EXISTS (
      SELECT 1 FROM order_discounts od WHERE od.order_id = o.id
    )
),
rated AS (
  SELECT
    os.tenant_id AS tenant_id,
    os.user_id AS user_id,
    os.order_id AS order_id,
    CASE
      WHEN os.subscription_status = 'canceled' THEN 'canceled'
      WHEN os.subscription_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN os.currency_code = 'EUR' THEN os.discount_total
      ELSE CAST(os.discount_total / er.rate AS NUMERIC(18,6))
    END AS discount_eur
  FROM order_sub os
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = os.currency_code
    AND er.as_of = CAST(os.placed_at AS date)
)
SELECT
  t.name AS tenant_name,
  r.status_category AS status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT r.user_id) AS distinct_customers,
  SUM(r.discount_eur) AS total_discount_eur
FROM rated r
JOIN tenants t ON t.id = r.tenant_id
GROUP BY t.name, r.status_category
ORDER BY t.name NULLS LAST, r.status_category NULLS LAST

Firebolt{ "errors": [ { "description": "Line 38, 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": "6b7ac9f0-c7fe-432b-9a80-1c52b988393d", "query_lab
DataFusionThis feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)
ClickHouseCode: 48. DB::Exception: Current query is not supported yet, because can't find correlated column '__table6.id' in current header: __table6.tenant_id, __table6.user_id, __table6.discount_total, __table6.currency_code, __table6.placed_at. (NOT_IMPLEMENTED)

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

The request, as Omar received it

Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):

Title: Reconciled full-history (2023-2025) orphan population by tenant and subscription status (single pass)
Question: Using the same single-pass, fan-out-safe construction, does the full-history orphan population by tenant and status reproduce the monthly trend's cumulative canceled-bucket total (~€57,658.66, roughly 1,188 orders by hand-summing last week's monthly report)?
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: Identical construction and definitions to the 2025-only version above (one row per order_id built first via subqueries/window functions, no fan-out joins, then aggregated), but over the full history: orders.placed_at from 2023-01-01 to 2025-12-31, status != 'cancelled'. Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). In the engineer's note, state the grand total order count and EUR total for the 'canceled' category across all tenants, and flag whether it's close to the reference figures above (~1,188 orders, ~€57,658.66) or diverges, with a hypothesis why if it diverges. Order by tenant name, then status category. Print all rows.

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

Omar calls dry_run

WITH order_sub AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.discount_total,
    o.currency_code,
    o.placed_at,
    (
      SELECT s.status
      FROM invoices i
      JOIN subscriptions s ON s.id = i.subscription_id
      WHERE i.order_id = o.id
      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC
      LIMIT 1
    ) AS subscription_status
  FROM orders o
  WHERE o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.discount_total > 0
    AND NOT EXISTS (
      SELECT 1 FROM order_discounts od WHERE od.order_id = o.id
    )
),
rated AS (
  SELECT
    os.tenant_id,
    os.user_id,
    os.order_id,
    CASE
      WHEN os.subscription_status = 'canceled' THEN 'canceled'
      WHEN os.subscription_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN os.currency_code = 'EUR' THEN os.discount_total
      ELSE os.discount_total / er.rate
    END AS discount_eur
  FROM order_sub os
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = os.currency_code
    AND er.as_of = os.placed_at::date
)
SELECT
  t.name AS tenant_name,
  r.status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT r.user_id) AS distinct_customers,
  ROUND(SUM(r.discount_eur), 2) AS total_discount_eur
FROM rated r
JOIN tenants t ON t.id = r.tenant_id
GROUP BY t.name, r.status_category
ORDER BY t.name, r.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_eur

Omar calls submit

WITH order_sub AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.discount_total,
    o.currency_code,
    o.placed_at,
    (
      SELECT s.status
      FROM invoices i
      JOIN subscriptions s ON s.id = i.subscription_id
      WHERE i.order_id = o.id
      ORDER BY i.issued_at DESC NULLS LAST, i.id DESC
      LIMIT 1
    ) AS subscription_status
  FROM orders o
  WHERE o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.discount_total > 0
    AND NOT EXISTS (
      SELECT 1 FROM order_discounts od WHERE od.order_id = o.id
    )
),
rated AS (
  SELECT
    os.tenant_id,
    os.user_id,
    os.order_id,
    CASE
      WHEN os.subscription_status = 'canceled' THEN 'canceled'
      WHEN os.subscription_status = 'active' THEN 'active'
      ELSE 'other-or-no-subscription-record'
    END AS status_category,
    CASE
      WHEN os.currency_code = 'EUR' THEN os.discount_total
      ELSE os.discount_total / er.rate
    END AS discount_eur
  FROM order_sub os
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = os.currency_code
    AND er.as_of = os.placed_at::date
)
SELECT
  t.name AS tenant_name,
  r.status_category,
  COUNT(*) AS orphan_order_count,
  COUNT(DISTINCT r.user_id) AS distinct_customers,
  ROUND(SUM(r.discount_eur), 2) AS total_discount_eur
FROM rated r
JOIN tenants t ON t.id = r.tenant_id
GROUP BY t.name, r.status_category
ORDER BY t.name, r.status_category

The warehouse answers

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

OmarSubmitted. Summary of what I did and why: - Built the orphan set in a single non-fan-out pass: a per-order subquery flags orders (2023‑01‑01 to 2025‑12‑31, `status <> 'cancelled'`) that have `discount_total > 0` but **no** corresponding `order_discounts` row (`NOT EXISTS`), which is the fan-out-safe way to do this since `order_discounts` can have multiple rows per order. - Attached each orphan order to the status of its most recent linked subscription (via `invoices.subscription_id`) using a scalar subquery (`ORDER BY issued_at DESC LIMIT 1`) — again avoiding any join fan-out — then bucketed into canceled / active / other-or-no-subscription-record. - Converted `discount_total` to EUR via `exchange_rates` (base EUR, quote = order currency, as_of = placed_at date), treating EUR orders as already in EUR. - Aggregated to tenant × status_category with order count, distinct customers, and EUR discount total, ordered by tenant then category. Flagged two caveats in the note: I had to infer the "orphan" definition since the earlier 2025-only request wasn't visible to me in this session, and as the engineer I never see query output (dry_run uses an empty schema), so I can't personally verify the ~1,188 orders / ~€57,658.66 reconciliation — that comparison needs to happen once the analyst has the printed report.

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 (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)

Reconciled full-history (2023-2025) orphan population by tenant and subscription status (single pass)

A business event lands on the Head of Pricing and Promotions's desk

Marco Bianchi writes to Oliver Grant

Oliver Grant plans the night

Omar writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q368

Everything, as recorded