Queries · q359 · Head of Pricing and Promotions · week 2, night 1

Is the 'active' 18% of orphan subscription-delivery orders a categorization artifact or a real gap?

“For the ~291 orphan free-delivery orders where the customer's most-recent subscription record shows status 'active', did that order actually fall in a gap between an earlier (now-superseded) subscription and the current one, which would mean it belongs with the confirmed 'canceled' leakage group rather than being a separate unexplained case?”

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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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 1, request 1 of 3
Marco wants the €57k lapsed-subscriber number airtight before it goes to Tobias, wants Welcome10's refund pattern explained one layer deeper, wants an honest size-matched incrementality attempt, and wants the leakage trend over time — four questions, three slots tonight, so I'm sequencing by what's blocking the Wednesday sync.

Priority tonight is the subscription-leakage story since Marco explicitly wants it checked before taking it upstairs, so two of three slots go there:
1. A methodology check on the 18%/€11.7k "active" bucket from last week — I suspect this isn't a real gap but an artifact of how I categorized customers by their single most-recent subscription record: if their current subscription started after the free order, there may well have been an older (now-superseded) subscription that actually covered the order date, which would mean it belongs with the "canceled/lapsed" leakage story rather than being unexplained. I want this resolved before stating a number to Tobias.
2. A monthly trend (full 2023-2025 history) on the confirmed "canceled-subscriber" leakage bucket, to tell Marco whether this is a slow-burning historical gap or getting worse — directly answers his accelerating-or-steady question.
Third slot goes to digging one layer into Welcome10's refund pattern (which category of products it clusters in, and whether return timing looks like fit/expectation mismatch vs. quick-flip behavior) — the one piece Marco called out as worth a deeper look before any capping conversation. I'm holding the harder, size-matched incrementality re-cut for night 2, since it needs more careful design and isn't needed before Wednesday's sync, whereas the subscription-leakage number is.

Also noting for context (not acting on tonight): Growth Marketing's company-wide finding that discounted orders return 5-7pts more with no basket-size boost in apparel/electronics lines up with our Welcome10 finding — useful external corroboration, not a new data pull.
Is the 'active' 18% of orphan subscription-delivery orders a categorization artifact or a real gap?
Question
For the ~291 orphan free-delivery orders where the customer's most-recent subscription record shows status 'active', did that order actually fall in a gap between an earlier (now-superseded) subscription and the current one, which would mean it belongs with the confirmed 'canceled' leakage group rather than being a separate unexplained case?
One row per
explanation category
Columns
- category: 'order preceded customer's first-ever subscription' / 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)' / 'unexplained - active subscription dates should already have overlapped'
- order count
- % of the 291 active-bucket orphan orders
- distinct customers
- total discount amount (EUR)

Bea 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 orphan_orders AS (
  SELECT o.id AS order_id, o.tenant_id, o.user_id, o.placed_at, od.amount AS discount_amount, od.currency_code AS discount_currency_code
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s0
      WHERE s0.tenant_id = o.tenant_id
        AND s0.user_id = o.user_id
        AND s0.status IN ('trialing','active','past_due')
        AND s0.started_at <= o.placed_at
        AND (s0.ended_at IS NULL OR s0.ended_at >= o.placed_at)
    )
),
most_recent_sub AS (
  SELECT DISTINCT ON (s.tenant_id, s.user_id)
    s.tenant_id, s.user_id, s.id AS sub_id, s.status
  FROM subscriptions s
  ORDER BY s.tenant_id, s.user_id, s.started_at DESC, s.id DESC
),
active_bucket_orders AS (
  SELECT oo.order_id, oo.tenant_id, oo.user_id, oo.placed_at,
         oo.discount_amount, oo.discount_currency_code,
         mrs.sub_id AS most_recent_sub_id
  FROM orphan_orders oo
  JOIN most_recent_sub mrs
    ON mrs.tenant_id = oo.tenant_id AND mrs.user_id = oo.user_id
  WHERE mrs.status = 'active'
),
older_sub_candidates AS (
  SELECT abo.order_id, s.id AS older_sub_id, s.started_at AS older_started_at, s.ended_at AS older_ended_at,
         ROW_NUMBER() OVER (PARTITION BY abo.order_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM active_bucket_orders abo
  JOIN subscriptions s
    ON s.tenant_id = abo.tenant_id
   AND s.user_id = abo.user_id
   AND s.id <> abo.most_recent_sub_id
   AND s.started_at <= abo.placed_at
),
older_sub_pick AS (
  SELECT order_id, older_sub_id, older_started_at, older_ended_at
  FROM older_sub_candidates
  WHERE rn = 1
),
classified AS (
  SELECT
    abo.order_id,
    abo.tenant_id,
    abo.user_id,
    abo.placed_at,
    abo.discount_amount,
    abo.discount_currency_code,
    CASE
      WHEN osp.older_sub_id IS NULL THEN 'order preceded customer''s first-ever subscription'
      WHEN osp.older_ended_at < abo.placed_at THEN 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)'
      ELSE 'unexplained - active subscription dates should already have overlapped'
    END AS category
  FROM active_bucket_orders abo
  LEFT JOIN older_sub_pick osp ON osp.order_id = abo.order_id
),
with_eur AS (
  SELECT
    c.order_id, c.tenant_id, c.user_id, c.category,
    c.discount_amount / er.rate AS discount_amount_eur
  FROM classified c
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = c.discount_currency_code
   AND er.as_of = DATE(c.placed_at)
),
total AS (
  SELECT COUNT(*) AS total_orders FROM active_bucket_orders
)
SELECT
  w.category,
  COUNT(*) AS order_count,
  ROUND(100.0 * COUNT(*) / t.total_orders, 1) AS pct_of_291_bucket,
  COUNT(DISTINCT w.user_id) AS distinct_customers,
  ROUND(SUM(w.discount_amount_eur), 2) AS total_discount_eur
FROM with_eur w
CROSS JOIN total t
GROUP BY w.category, t.total_orders
ORDER BY order_count DESC, w.category

“The query compiles and returns the expected grain (one row per category) with the 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 classifies the ~291 active-bucket orphan orders into the three explanation categories (preceded first subscription, fell between stints, or truly unexplained), with order count, share of the bucket, distinct customers, and total discount in EUR for each — ordered by order count descending as requested.”

The report comes back overnight

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

category                                 | order_count | pct_of_291_bucket | distinct_customers | total_discount_eur
-----------------------------------------+-------------+-------------------+--------------------+-------------------
unexplained - active subscription dates  |          88 |              30.2 |                 19 |            3363.71
(1 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q362Corrected categorization of all 291 active-bucket orphan subscription-delivery orders
It builds on:
- q157Subscription status distribution behind orphan comped-delivery orders

It becomes benchmark query q359

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

editDISTINCT ON is a PostgreSQL extension: the first row per key written as ROW_NUMBER() = 1, standard SQL.
spellingdate cast
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 (24×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (6×)

The benchmark text, against the submitted SQL

WITH orphan_orders AS (  SELECT o.id AS order_id, o.tenant_id, o.user_id, o.placed_at, od.amount AS discount_amount, od.currency_code AS discount_currency_code  SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.placed_at AS placed_at, od.amount AS discount_amount, od.currency_code AS discount_currency_code  FROM orders o  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id  WHERE d.name = 'Subscription delivery (prepaid)'    AND o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'    AND NOT EXISTS (      SELECT 1 FROM subscriptions s0      WHERE s0.tenant_id = o.tenant_id        AND s0.user_id = o.user_id        AND s0.status IN ('trialing','active','past_due')        AND s0.started_at <= o.placed_at        AND (s0.ended_at IS NULL OR s0.ended_at >= o.placed_at)    )),most_recent_sub AS (  SELECT DISTINCT ON (s.tenant_id, s.user_id)    s.tenant_id, s.user_id, s.id AS sub_id, s.status  FROM subscriptions s  ORDER BY s.tenant_id, s.user_id, s.started_at DESC, s.id DESC  SELECT tenant_id, user_id, sub_id, status  FROM (    SELECT s.tenant_id AS tenant_id, s.user_id AS user_id, s.id AS sub_id, s.status AS status,           ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC NULLS FIRST, s.id DESC NULLS FIRST) AS rn    FROM subscriptions s  ) ranked  WHERE rn = 1),active_bucket_orders AS (  SELECT oo.order_id, oo.tenant_id, oo.user_id, oo.placed_at,         oo.discount_amount, oo.discount_currency_code,  SELECT oo.order_id AS order_id, oo.tenant_id AS tenant_id, oo.user_id AS user_id, oo.placed_at AS placed_at,         oo.discount_amount AS discount_amount, oo.discount_currency_code AS discount_currency_code,         mrs.sub_id AS most_recent_sub_id  FROM orphan_orders oo  JOIN most_recent_sub mrs    ON mrs.tenant_id = oo.tenant_id AND mrs.user_id = oo.user_id  WHERE mrs.status = 'active'),older_sub_candidates AS (  SELECT abo.order_id, s.id AS older_sub_id, s.started_at AS older_started_at, s.ended_at AS older_ended_at,         ROW_NUMBER() OVER (PARTITION BY abo.order_id ORDER BY s.started_at DESC, s.id DESC) AS rn  SELECT abo.order_id AS order_id, s.id AS older_sub_id, s.started_at AS older_started_at, s.ended_at AS older_ended_at,         ROW_NUMBER() OVER (PARTITION BY abo.order_id ORDER BY s.started_at DESC NULLS FIRST, s.id DESC NULLS FIRST) AS rn  FROM active_bucket_orders abo  JOIN subscriptions s    ON s.tenant_id = abo.tenant_id   AND s.user_id = abo.user_id   AND s.id <> abo.most_recent_sub_id   AND s.started_at <= abo.placed_at),older_sub_pick AS (  SELECT order_id, older_sub_id, older_started_at, older_ended_at  FROM older_sub_candidates  WHERE rn = 1),classified AS (  SELECT    abo.order_id,    abo.tenant_id,    abo.user_id,    abo.placed_at,    abo.discount_amount,    abo.discount_currency_code,    abo.order_id AS order_id,    abo.tenant_id AS tenant_id,    abo.user_id AS user_id,    abo.placed_at AS placed_at,    abo.discount_amount AS discount_amount,    abo.discount_currency_code AS discount_currency_code,    CASE      WHEN osp.older_sub_id IS NULL THEN 'order preceded customer''s first-ever subscription'      WHEN osp.older_ended_at < abo.placed_at THEN 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)'      ELSE 'unexplained - active subscription dates should already have overlapped'    END AS category  FROM active_bucket_orders abo  LEFT JOIN older_sub_pick osp ON osp.order_id = abo.order_id),with_eur AS (  SELECT    c.order_id, c.tenant_id, c.user_id, c.category,    c.discount_amount / er.rate AS discount_amount_eur    c.order_id AS order_id, c.tenant_id AS tenant_id, c.user_id AS user_id, c.category AS category,    CAST(c.discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur  FROM classified c  JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = c.discount_currency_code   AND er.as_of = DATE(c.placed_at)   AND er.as_of = CAST(c.placed_at AS DATE)),total AS (  SELECT COUNT(*) AS total_orders FROM active_bucket_orders)SELECT  w.category,  w.category AS category,  COUNT(*) AS order_count,  ROUND(100.0 * COUNT(*) / t.total_orders, 1) AS pct_of_291_bucket,  100.0 * COUNT(*) / t.total_orders AS pct_of_291_bucket,  COUNT(DISTINCT w.user_id) AS distinct_customers,  ROUND(SUM(w.discount_amount_eur), 2) AS total_discount_eur  SUM(w.discount_amount_eur) AS total_discount_eurFROM with_eur wCROSS JOIN total tGROUP BY w.category, t.total_ordersORDER BY order_count DESC, w.categoryORDER BY order_count DESC NULLS FIRST, w.category NULLS LAST

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

WITH orphan_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, od.amount AS discount_amount, od.currency_code AS discount_currency_code
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s0
      WHERE s0.tenant_id = o.tenant_id
        AND s0.user_id = o.user_id
        AND s0.status IN ('trialing','active','past_due')
        AND s0.started_at <= o.placed_at
        AND (s0.ended_at IS NULL OR s0.ended_at >= o.placed_at)
    )
),
most_recent_sub AS (
  SELECT tenant_id, user_id, sub_id, status
  FROM (
    SELECT s.tenant_id AS tenant_id, s.user_id AS user_id, s.id AS sub_id, s.status AS status,
           ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC NULLS FIRST, s.id DESC NULLS FIRST) AS rn
    FROM subscriptions s
  ) ranked
  WHERE rn = 1
),
active_bucket_orders AS (
  SELECT oo.order_id AS order_id, oo.tenant_id AS tenant_id, oo.user_id AS user_id, oo.placed_at AS placed_at,
         oo.discount_amount AS discount_amount, oo.discount_currency_code AS discount_currency_code,
         mrs.sub_id AS most_recent_sub_id
  FROM orphan_orders oo
  JOIN most_recent_sub mrs
    ON mrs.tenant_id = oo.tenant_id AND mrs.user_id = oo.user_id
  WHERE mrs.status = 'active'
),
older_sub_candidates AS (
  SELECT abo.order_id AS order_id, s.id AS older_sub_id, s.started_at AS older_started_at, s.ended_at AS older_ended_at,
         ROW_NUMBER() OVER (PARTITION BY abo.order_id ORDER BY s.started_at DESC NULLS FIRST, s.id DESC NULLS FIRST) AS rn
  FROM active_bucket_orders abo
  JOIN subscriptions s
    ON s.tenant_id = abo.tenant_id
   AND s.user_id = abo.user_id
   AND s.id <> abo.most_recent_sub_id
   AND s.started_at <= abo.placed_at
),
older_sub_pick AS (
  SELECT order_id, older_sub_id, older_started_at, older_ended_at
  FROM older_sub_candidates
  WHERE rn = 1
),
classified AS (
  SELECT
    abo.order_id AS order_id,
    abo.tenant_id AS tenant_id,
    abo.user_id AS user_id,
    abo.placed_at AS placed_at,
    abo.discount_amount AS discount_amount,
    abo.discount_currency_code AS discount_currency_code,
    CASE
      WHEN osp.older_sub_id IS NULL THEN 'order preceded customer''s first-ever subscription'
      WHEN osp.older_ended_at < abo.placed_at THEN 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)'
      ELSE 'unexplained - active subscription dates should already have overlapped'
    END AS category
  FROM active_bucket_orders abo
  LEFT JOIN older_sub_pick osp ON osp.order_id = abo.order_id
),
with_eur AS (
  SELECT
    c.order_id AS order_id, c.tenant_id AS tenant_id, c.user_id AS user_id, c.category AS category,
    CAST(c.discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur
  FROM classified c
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = c.discount_currency_code
   AND er.as_of = CAST(c.placed_at AS DATE)
),
total AS (
  SELECT COUNT(*) AS total_orders FROM active_bucket_orders
)
SELECT
  w.category AS category,
  COUNT(*) AS order_count,
  100.0 * COUNT(*) / t.total_orders AS pct_of_291_bucket,
  COUNT(DISTINCT w.user_id) AS distinct_customers,
  SUM(w.discount_amount_eur) AS total_discount_eur
FROM with_eur w
CROSS JOIN total t
GROUP BY w.category, t.total_orders
ORDER BY order_count DESC NULLS FIRST, w.category NULLS LAST

Firebolt{ "errors": [ { "description": "Line 71, Column 28: 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": "d589dc16-da68-4577-ae49-0dd3328652da", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table2.discount_amount Decimal(14, 4) Decimal64(size = 0), __table28.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

Bea's whole session, 7 turns

The request, as Bea received it

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

Title: Is the 'active' 18% of orphan subscription-delivery orders a categorization artifact or a real gap?
Question: For the ~291 orphan free-delivery orders where the customer's most-recent subscription record shows status 'active', did that order actually fall in a gap between an earlier (now-superseded) subscription and the current one, which would mean it belongs with the confirmed 'canceled' leakage group rather than being a separate unexplained case?
Grain: one row per explanation category
Columns:
- category: 'order preceded customer's first-ever subscription' / 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)' / 'unexplained - active subscription dates should already have overlapped'
- order count
- % of the 291 active-bucket orphan orders
- distinct customers
- total discount amount (EUR)
Details: Source: start from the exact orphan-order population used in last week's reports (order_discounts + discounts name='Subscription delivery (prepaid)' + orders placed_at in 2025, status != 'cancelled', no overlapping subscription with status in trialing/active/past_due for that user+tenant), restricted to the subset whose most-recent subscription (by started_at) has status = 'active' (the 291-order/60-customer group from last week, ~€11,746). For each such order, check: (a) does the customer have any OTHER subscription row for that tenant (any status) with started_at <= order.placed_at? If yes and that older subscription's own coverage (started_at to ended_at, or started_at to current date if still open) does not include placed_at, classify as 'fell between two subscription stints'. If no such older row exists at all, classify as 'preceded first-ever subscription'. Otherwise ('unexplained') flag for manual review. Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Order by order count descending. Print all categories (expect 3 rows).

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

Bea calls dry_run

WITH orphan_orders AS (
  SELECT o.id AS order_id, o.tenant_id, o.user_id, o.placed_at, od.amount AS discount_amount, od.currency_code AS discount_currency_code
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s0
      WHERE s0.tenant_id = o.tenant_id
        AND s0.user_id = o.user_id
        AND s0.status IN ('trialing','active','past_due')
        AND s0.started_at <= o.placed_at
        AND (s0.ended_at IS NULL OR s0.ended_at >= o.placed_at)
    )
),
most_recent_sub AS (
  SELECT DISTINCT ON (s.tenant_id, s.user_id)
    s.tenant_id, s.user_id, s.id AS sub_id, s.status
  FROM subscriptions s
  ORDER BY s.tenant_id, s.user_id, s.started_at DESC, s.id DESC
),
active_bucket_orders AS (
  SELECT oo.order_id, oo.tenant_id, oo.user_id, oo.placed_at,
         oo.discount_amount, oo.discount_currency_code,
         mrs.sub_id AS most_recent_sub_id
  FROM orphan_orders oo
  JOIN most_recent_sub mrs
    ON mrs.tenant_id = oo.tenant_id AND mrs.user_id = oo.user_id
  WHERE mrs.status = 'active'
),
older_sub_candidates AS (
  SELECT abo.order_id, s.id AS older_sub_id, s.started_at AS older_started_at, s.ended_at AS older_ended_at,
         ROW_NUMBER() OVER (PARTITION BY abo.order_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM active_bucket_orders abo
  JOIN subscriptions s
    ON s.tenant_id = abo.tenant_id
   AND s.user_id = abo.user_id
   AND s.id <> abo.most_recent_sub_id
   AND s.started_at <= abo.placed_at
),
older_sub_pick AS (
  SELECT order_id, older_sub_id, older_started_at, older_ended_at
  FROM older_sub_candidates
  WHERE rn = 1
),
classified AS (
  SELECT
    abo.order_id,
    abo.tenant_id,
    abo.user_id,
    abo.placed_at,
    abo.discount_amount,
    abo.discount_currency_code,
    CASE
      WHEN osp.older_sub_id IS NULL THEN 'order preceded customer''s first-ever subscription'
      WHEN osp.older_ended_at < abo.placed_at THEN 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)'
      ELSE 'unexplained - active subscription dates should already have overlapped'
    END AS category
  FROM active_bucket_orders abo
  LEFT JOIN older_sub_pick osp ON osp.order_id = abo.order_id
),
with_eur AS (
  SELECT
    c.order_id, c.tenant_id, c.user_id, c.category,
    c.discount_amount / er.rate AS discount_amount_eur
  FROM classified c
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = c.discount_currency_code
   AND er.as_of = DATE(c.placed_at)
),
total AS (
  SELECT COUNT(*) AS total_orders FROM active_bucket_orders
)
SELECT
  w.category,
  COUNT(*) AS order_count,
  ROUND(100.0 * COUNT(*) / t.total_orders, 1) AS pct_of_291_bucket,
  COUNT(DISTINCT w.user_id) AS distinct_customers,
  ROUND(SUM(w.discount_amount_eur), 2) AS total_discount_eur
FROM with_eur w
CROSS JOIN total t
GROUP BY w.category, t.total_orders
ORDER BY order_count DESC, w.category

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: category, order_count, pct_of_291_bucket, distinct_customers, total_discount_eur

BeaThe query compiles and returns the expected grain (one row per category) with the requested columns. Submitting.

Bea calls submit

WITH orphan_orders AS (
  SELECT o.id AS order_id, o.tenant_id, o.user_id, o.placed_at, od.amount AS discount_amount, od.currency_code AS discount_currency_code
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s0
      WHERE s0.tenant_id = o.tenant_id
        AND s0.user_id = o.user_id
        AND s0.status IN ('trialing','active','past_due')
        AND s0.started_at <= o.placed_at
        AND (s0.ended_at IS NULL OR s0.ended_at >= o.placed_at)
    )
),
most_recent_sub AS (
  SELECT DISTINCT ON (s.tenant_id, s.user_id)
    s.tenant_id, s.user_id, s.id AS sub_id, s.status
  FROM subscriptions s
  ORDER BY s.tenant_id, s.user_id, s.started_at DESC, s.id DESC
),
active_bucket_orders AS (
  SELECT oo.order_id, oo.tenant_id, oo.user_id, oo.placed_at,
         oo.discount_amount, oo.discount_currency_code,
         mrs.sub_id AS most_recent_sub_id
  FROM orphan_orders oo
  JOIN most_recent_sub mrs
    ON mrs.tenant_id = oo.tenant_id AND mrs.user_id = oo.user_id
  WHERE mrs.status = 'active'
),
older_sub_candidates AS (
  SELECT abo.order_id, s.id AS older_sub_id, s.started_at AS older_started_at, s.ended_at AS older_ended_at,
         ROW_NUMBER() OVER (PARTITION BY abo.order_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM active_bucket_orders abo
  JOIN subscriptions s
    ON s.tenant_id = abo.tenant_id
   AND s.user_id = abo.user_id
   AND s.id <> abo.most_recent_sub_id
   AND s.started_at <= abo.placed_at
),
older_sub_pick AS (
  SELECT order_id, older_sub_id, older_started_at, older_ended_at
  FROM older_sub_candidates
  WHERE rn = 1
),
classified AS (
  SELECT
    abo.order_id,
    abo.tenant_id,
    abo.user_id,
    abo.placed_at,
    abo.discount_amount,
    abo.discount_currency_code,
    CASE
      WHEN osp.older_sub_id IS NULL THEN 'order preceded customer''s first-ever subscription'
      WHEN osp.older_ended_at < abo.placed_at THEN 'order fell between two subscription stints (an older subscription record exists with started_at <= order date)'
      ELSE 'unexplained - active subscription dates should already have overlapped'
    END AS category
  FROM active_bucket_orders abo
  LEFT JOIN older_sub_pick osp ON osp.order_id = abo.order_id
),
with_eur AS (
  SELECT
    c.order_id, c.tenant_id, c.user_id, c.category,
    c.discount_amount / er.rate AS discount_amount_eur
  FROM classified c
  JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = c.discount_currency_code
   AND er.as_of = DATE(c.placed_at)
),
total AS (
  SELECT COUNT(*) AS total_orders FROM active_bucket_orders
)
SELECT
  w.category,
  COUNT(*) AS order_count,
  ROUND(100.0 * COUNT(*) / t.total_orders, 1) AS pct_of_291_bucket,
  COUNT(DISTINCT w.user_id) AS distinct_customers,
  ROUND(SUM(w.discount_amount_eur), 2) AS total_discount_eur
FROM with_eur w
CROSS JOIN total t
GROUP BY w.category, t.total_orders
ORDER BY order_count DESC, w.category

The warehouse answers

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

BeaSubmitted. The report classifies the ~291 active-bucket orphan orders into the three explanation categories (preceded first subscription, fell between stints, or truly unexplained), with order count, share of the bucket, distinct customers, and total discount in EUR for each — ordered by order count descending as requested.

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

Bea's transcript (297 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (7 KB) · the night's reports (9 KB) · the week's memo (3 KB)

Is the 'active' 18% of orphan subscription-delivery orders a categorization artifact or a real gap?

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

Marco Bianchi writes to Oliver Grant

Oliver Grant plans the night

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q359

Everything, as recorded