Queries · q157 · Head of Pricing and Promotions · week 1, night 4

Subscription status distribution behind orphan comped-delivery orders

“What subscription status do the 'orphan' Subscription-delivery-discount customers (no active subscription overlap found) actually have on record, since neither 'lapsed within 30 days' nor 'no subscription at all' explained them?”

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 1
Subject: Priorities for this week's discount cost-review with Tobias

Oliver,

Tobias and I need a credible answer to "are our promotions and coupons earning their keep," in time for Friday's memo. This feeds a joint decision on which promotions/coupons we keep, retire, or cap going into next quarter — so I'd rather have four solid, well-checked answers than a pile of rushed ones. Please pick the nights' reports accordingly and flag anything suspicious you find along the way rather than burying it in a footnote.

Here's what I actually want to know, in my own words:
1. Which promotions and coupons are costing us the most, and is that cost buying us anything? I want to see discount cost alongside the orders/revenue they sit on, so we can tell "big spend, big lift" apart from "big spend, no lift."
2. For our top discounts, do customers actually spend more because of them, or are we just discounting purchases that would have happened anyway? I know this is the hard one — I just want our best honest read on it, even if approximate.
3. Are any coupons being overused or abused — the same customers redeeming repeatedly, usage patterns that look like stacking or sharing of codes meant to be one-time or limited, or codes that are far outliving the campaign they were meant for?
4. Which discounts are quietly eating into margin on top of their price reduction — for example cases where returns or refunds cluster around discounted orders, so the real cost is higher than the discount line alone suggests.
5. (If there's a slot left) How does this differ by merchant or vertical — are one or two tenants responsible for most of the discount spend, which would change how we approach this.
Keep each report to one clear question — I'd rather get three clean answers this week than one bundled one that gets bounced. Let's sync briefly before you submit Thursday's batch in case the first results change what we ask.

Thanks, Marco
A note from the Chief Financial Officer, Tobias Lindqvist
Subject: Finance's angle on the promotions/coupons review

Oliver,

As you and Marco dig into discount performance this week, a few things I'd like the analysis to keep an eye on from the finance side:
1. Are discounted orders actually incremental, or just margin we're giving away on sales that would have happened anyway? I care less about redemption volume and more about whether promotions are growing net revenue after the discount, versus simply lowering what we collect on existing demand.
2. Is any of this exposed as cost we haven't fully absorbed yet? Where coupons stack with refunds or returns, I want to understand whether we're left carrying a loss after the fact, especially heading into year-end close.
3. Is there any pattern of abuse or concentration — the same accounts or a narrow set of merchants repeatedly running the heaviest discounts — that's quietly eating into margin rather than driving genuine growth?
Keep it tight; I mainly need the "so what" for the annual accounts and the 2026 budget assumptions on discount cost.

Thanks, Tobias
Oliver Grant plans the night
The analyst · week 1, night 4, request 1 of 3
Last night before the memo, so I'm closing out the three loose threads that actually change what I tell Marco and Tobias, and skipping anything merely confirmatory:
1. The orphan subscription-delivery report came back strange: 0% of orphan customers were classified as "lapsed within 30 days" and 0% as "no subscription record at all" — yet 25-46% of orders are orphan. That means essentially all orphan customers have a subscription record, just not one whose status/date my query treated as active-and-overlapping. The likely candidates are 'paused' or 'incomplete' subscriptions, or cancellations that happened well before the 30-day window I checked. I need the actual status distribution before I can tell Tobias whether this is "paused customers still getting free boxes" (a real control gap) or something more benign.
2. The corrected (pre-discount subtotal) lift report is strikingly consistent — every one of the 50 worst rows shows negative lift, and the pattern looks structural, not noise. But it's fragmented into 219 rows at the per-tenant-instance grain, which is too granular for the memo's headline number. I want one clean, order-weighted summary per promotion family (Welcome10 / Black Friday / January Sale / Summer Sale) so Marco and Tobias get a single defensible "no incremental lift, if anything smaller baskets" figure rather than a page of tenant fragments.
3. The returns/refunds report surfaced two outsized ratios — a Welcome10 instance refunding 189% of its own discount cost, and a Volume-tier-10+ instance refunding 162% of its discount cost. Before stating "this discount is actively loss-making after refunds" in the memo, I want to check whether that's driven by a handful of large refunds (an outlier/concentration story, possibly one big account) versus broad-based returns across many orders (a genuine pattern worth capping the discount over).
These three reports close out Marco's Q3/Q4 and Tobias's Q1/Q2 with concrete, checked numbers for Friday.
Subscription status distribution behind orphan comped-delivery orders
Question
What subscription status do the 'orphan' Subscription-delivery-discount customers (no active subscription overlap found) actually have on record, since neither 'lapsed within 30 days' nor 'no subscription at all' explained them?
One row per
subscription-status category (trialing, active, past_due, canceled, paused, incomplete, or 'no_subscription_record')
Columns
- status category (customer's most recent subscription status for that tenant, by started_at, or 'no_subscription_record' if none exists)
- orphan customer count
- orphan order count (2025 'Subscription delivery (prepaid)' orders with no active-status date overlap, as defined in the prior report)
- % of all orphan orders
- avg days between subscription's ended_at (or current date if still open) and the orphan order's placed_at, where applicable
- total discount amount on these orders (EUR)

Bea writes the SQL

The engineer · 2 dry runs on the empty schema, then the submission

Dry run 1 · STATUS: REJECTED

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
  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 s
      WHERE s.tenant_id = o.tenant_id
        AND s.user_id = o.user_id
        AND s.status IN ('trialing','active','past_due')
        AND s.started_at <= o.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)
    )
),
latest_subscription AS (
  SELECT
    s.tenant_id AS tenant_id,
    s.user_id AS user_id,
    s.status AS status,
    s.ended_at AS ended_at,
    s.started_at AS started_at,
    ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM subscriptions s
),
orphan_with_category 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 AS discount_currency,
    COALESCE(ls.status, 'no_subscription_record') AS status_category,
    ls.ended_at AS sub_ended_at
  FROM orphan_orders oo
  LEFT JOIN latest_subscription ls
    ON ls.tenant_id = oo.tenant_id
   AND ls.user_id = oo.user_id
   AND ls.rn = 1
),
t0 AS (
  SELECT DISTINCT status_category FROM orphan_with_category
),
t1 AS (
  SELECT status_category, COUNT(DISTINCT tenant_id, user_id) AS orphan_customer_count
  FROM orphan_with_category
  GROUP BY status_category
),
t2 AS (
  SELECT status_category, COUNT(*) AS orphan_order_count
  FROM orphan_with_category
  GROUP BY status_category
),
t3 AS (
  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category
),
t4 AS (
  SELECT status_category,
         AVG(COALESCE(sub_ended_at::date, DATE '2026-01-05') - placed_at::date) AS avg_days_sub_end_to_order
  FROM orphan_with_category
  WHERE status_category <> 'no_subscription_record'
  GROUP BY status_category
),
t5 AS (
  SELECT ow.status_category,
         SUM(
           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount
                ELSE ow.discount_amount / er.rate
           END
         ) AS total_discount_eur
  FROM orphan_with_category ow
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ow.discount_currency
   AND er.as_of = ow.placed_at::date
  GROUP BY ow.status_category
)
SELECT
  t0.status_category AS status_category,
  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,
  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,
  ROUND(100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0), 2) AS pct_of_all_orphan_orders,
  ROUND(t4.avg_days_sub_end_to_order, 1) AS avg_days_sub_end_to_order,
  ROUND(COALESCE(t5.total_discount_eur, 0), 2) AS total_discount_amount_eur
FROM t0
LEFT JOIN t1 ON t1.status_category = t0.status_category
LEFT JOIN t2 ON t2.status_category = t0.status_category
CROSS JOIN t3
LEFT JOIN t4 ON t4.status_category = t0.status_category
LEFT JOIN t5 ON t5.status_category = t0.status_category
ORDER BY COALESCE(t2.orphan_order_count, 0) DESC, t0.status_category ASC

Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

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  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 s      WHERE s.tenant_id = o.tenant_id        AND s.user_id = o.user_id        AND s.status IN ('trialing','active','past_due')        AND s.started_at <= o.placed_at        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)    )),latest_subscription AS (  SELECT    s.tenant_id AS tenant_id,    s.user_id AS user_id,    s.status AS status,    s.ended_at AS ended_at,    s.started_at AS started_at,    ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC, s.id DESC) AS rn  FROM subscriptions s),orphan_with_category 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 AS discount_currency,    COALESCE(ls.status, 'no_subscription_record') AS status_category,    ls.ended_at AS sub_ended_at  FROM orphan_orders oo  LEFT JOIN latest_subscription ls    ON ls.tenant_id = oo.tenant_id   AND ls.user_id = oo.user_id   AND ls.rn = 1),t0 AS (  SELECT DISTINCT status_category FROM orphan_with_category),t1 AS (  SELECT status_category, COUNT(DISTINCT tenant_id, user_id) AS orphan_customer_count  FROM orphan_with_category  SELECT status_category, COUNT(*) AS orphan_customer_count  FROM (    SELECT DISTINCT status_category, tenant_id, user_id    FROM orphan_with_category  ) dc  GROUP BY status_category),t2 AS (  SELECT status_category, COUNT(*) AS orphan_order_count  FROM orphan_with_category  GROUP BY status_category),t3 AS (  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category),t4 AS (  SELECT status_category,         AVG(COALESCE(sub_ended_at::date, DATE '2026-01-05') - placed_at::date) AS avg_days_sub_end_to_order  FROM orphan_with_category  WHERE status_category <> 'no_subscription_record'  GROUP BY status_category),t5 AS (  SELECT ow.status_category,         SUM(           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount                ELSE ow.discount_amount / er.rate           END         ) AS total_discount_eur  FROM orphan_with_category ow  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = ow.discount_currency   AND er.as_of = ow.placed_at::date  GROUP BY ow.status_category)SELECT  t0.status_category AS status_category,  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,  ROUND(100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0), 2) AS pct_of_all_orphan_orders,  ROUND(t4.avg_days_sub_end_to_order, 1) AS avg_days_sub_end_to_order,  ROUND(COALESCE(t5.total_discount_eur, 0), 2) AS total_discount_amount_eurFROM t0LEFT JOIN t1 ON t1.status_category = t0.status_categoryLEFT JOIN t2 ON t2.status_category = t0.status_categoryCROSS JOIN t3LEFT JOIN t4 ON t4.status_category = t0.status_categoryLEFT JOIN t5 ON t5.status_category = t0.status_categoryORDER BY COALESCE(t2.orphan_order_count, 0) DESC, t0.status_category ASC

“The query compiles cleanly with exactly 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. Summary of what the report delivers: - Grain: one row per subscription-status category (trialing / active / past_due / canceled / paused / incomplete / no_subscription_record), derived from each orphan customer's single most recent subscription record (any status) by started_at. - Orphan population: orders with a 'Subscription delivery (prepaid)' discount, placed in 2025, status ≠ cancelled, and no subscription in ('trialing','active','past_due') whose date range covers the order's placed_at — matching the prior report's definition. - Columns: status category, orphan customer count, orphan order count, % of all orphan orders, avg days from subscription ended_at (or 2026-01-05 if still open) to the order's placed_at (null for no_subscription_record, since it doesn't apply), and total discount amount converted to EUR via same-day exchange_rates. - Ordered by orphan order count descending, tie-broken by category name for determinism. One judgment call noted in the submit note: since the exact "prior report" SQL wasn't available to me, I reconstructed the overlap/orphan logic directly from the definition given in this request.”

The report comes back overnight

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

status_category | orphan_customer_count | orphan_order_count | pct_of_all_orphan_orders | avg_days_sub_end_to_order | total_discount_amount_eur
----------------+-----------------------+--------------------+--------------------------+---------------------------+--------------------------
canceled        |                   238 |               1322 |                    81.96 |                     116.9 |                  57167.69
active          |                    60 |                291 |                    18.04 |                     248.8 |                  11746.39
(2 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:
- q359Is the 'active' 18% of orphan subscription-delivery orders a categorization artifact or a real gap?
- q360Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025
It builds on:
- q154Subscription-delivery discount orders with no overlapping active subscription

It becomes benchmark query q157

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

spellingcast (3×)
numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
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
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×)

The benchmark text, against the submitted SQL

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  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 s      WHERE s.tenant_id = o.tenant_id        AND s.user_id = o.user_id        AND s.status IN ('trialing','active','past_due')        AND s.started_at <= o.placed_at        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)    )),latest_subscription AS (  SELECT    s.tenant_id AS tenant_id,    s.user_id AS user_id,    s.status AS status,    s.ended_at AS ended_at,    s.started_at AS started_at,    ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC, s.id DESC) AS rn    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),orphan_with_category 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 AS discount_currency,    COALESCE(ls.status, 'no_subscription_record') AS status_category,    ls.ended_at AS sub_ended_at  FROM orphan_orders oo  LEFT JOIN latest_subscription ls    ON ls.tenant_id = oo.tenant_id   AND ls.user_id = oo.user_id   AND ls.rn = 1),t0 AS (  SELECT DISTINCT status_category FROM orphan_with_category),t1 AS (  SELECT status_category, COUNT(*) AS orphan_customer_count  FROM (    SELECT DISTINCT status_category, tenant_id, user_id    FROM orphan_with_category  ) dc  GROUP BY status_category),t2 AS (  SELECT status_category, COUNT(*) AS orphan_order_count  FROM orphan_with_category  GROUP BY status_category),t3 AS (  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category),t4 AS (  SELECT status_category,         AVG(COALESCE(sub_ended_at::date, DATE '2026-01-05') - placed_at::date) AS avg_days_sub_end_to_order         AVG(CAST(COALESCE(CAST(sub_ended_at AS date), DATE '2026-01-05') - CAST(placed_at AS date) AS NUMERIC(18,6))) AS avg_days_sub_end_to_order  FROM orphan_with_category  WHERE status_category <> 'no_subscription_record'  GROUP BY status_category),t5 AS (  SELECT ow.status_category,  SELECT ow.status_category AS status_category,         SUM(           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount                ELSE ow.discount_amount / er.rate                ELSE CAST(ow.discount_amount / er.rate AS NUMERIC(18,6))           END         ) AS total_discount_eur  FROM orphan_with_category ow  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = ow.discount_currency   AND er.as_of = ow.placed_at::date   AND er.as_of = CAST(ow.placed_at AS date)  GROUP BY ow.status_category)SELECT  t0.status_category AS status_category,  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,  ROUND(100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0), 2) AS pct_of_all_orphan_orders,  ROUND(t4.avg_days_sub_end_to_order, 1) AS avg_days_sub_end_to_order,  ROUND(COALESCE(t5.total_discount_eur, 0), 2) AS total_discount_amount_eur  100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0) AS pct_of_all_orphan_orders,  t4.avg_days_sub_end_to_order AS avg_days_sub_end_to_order,  COALESCE(t5.total_discount_eur, 0) AS total_discount_amount_eurFROM t0LEFT JOIN t1 ON t1.status_category = t0.status_categoryLEFT JOIN t2 ON t2.status_category = t0.status_categoryCROSS JOIN t3LEFT JOIN t4 ON t4.status_category = t0.status_categoryLEFT JOIN t5 ON t5.status_category = t0.status_categoryORDER BY COALESCE(t2.orphan_order_count, 0) DESC, t0.status_category ASCORDER BY COALESCE(t2.orphan_order_count, 0) DESC NULLS FIRST, t0.status_category ASC 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
  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 s
      WHERE s.tenant_id = o.tenant_id
        AND s.user_id = o.user_id
        AND s.status IN ('trialing','active','past_due')
        AND s.started_at <= o.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)
    )
),
latest_subscription AS (
  SELECT
    s.tenant_id AS tenant_id,
    s.user_id AS user_id,
    s.status AS status,
    s.ended_at AS ended_at,
    s.started_at AS started_at,
    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
),
orphan_with_category 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 AS discount_currency,
    COALESCE(ls.status, 'no_subscription_record') AS status_category,
    ls.ended_at AS sub_ended_at
  FROM orphan_orders oo
  LEFT JOIN latest_subscription ls
    ON ls.tenant_id = oo.tenant_id
   AND ls.user_id = oo.user_id
   AND ls.rn = 1
),
t0 AS (
  SELECT DISTINCT status_category FROM orphan_with_category
),
t1 AS (
  SELECT status_category, COUNT(*) AS orphan_customer_count
  FROM (
    SELECT DISTINCT status_category, tenant_id, user_id
    FROM orphan_with_category
  ) dc
  GROUP BY status_category
),
t2 AS (
  SELECT status_category, COUNT(*) AS orphan_order_count
  FROM orphan_with_category
  GROUP BY status_category
),
t3 AS (
  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category
),
t4 AS (
  SELECT status_category,
         AVG(CAST(COALESCE(CAST(sub_ended_at AS date), DATE '2026-01-05') - CAST(placed_at AS date) AS NUMERIC(18,6))) AS avg_days_sub_end_to_order
  FROM orphan_with_category
  WHERE status_category <> 'no_subscription_record'
  GROUP BY status_category
),
t5 AS (
  SELECT ow.status_category AS status_category,
         SUM(
           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount
                ELSE CAST(ow.discount_amount / er.rate AS NUMERIC(18,6))
           END
         ) AS total_discount_eur
  FROM orphan_with_category ow
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ow.discount_currency
   AND er.as_of = CAST(ow.placed_at AS date)
  GROUP BY ow.status_category
)
SELECT
  t0.status_category AS status_category,
  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,
  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,
  100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0) AS pct_of_all_orphan_orders,
  t4.avg_days_sub_end_to_order AS avg_days_sub_end_to_order,
  COALESCE(t5.total_discount_eur, 0) AS total_discount_amount_eur
FROM t0
LEFT JOIN t1 ON t1.status_category = t0.status_category
LEFT JOIN t2 ON t2.status_category = t0.status_category
CROSS JOIN t3
LEFT JOIN t4 ON t4.status_category = t0.status_category
LEFT JOIN t5 ON t5.status_category = t0.status_category
ORDER BY COALESCE(t2.orphan_order_count, 0) DESC NULLS FIRST, t0.status_category ASC NULLS LAST

Firebolt{ "errors": [ { "description": "Line 86, Column 46: 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": "66dfea75-61ab-46df-bc46-e680ab37dd31", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table58.discount_amount Decimal(14, 4) Decimal64(size = 0), __table68.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_B

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

The request, as Bea received it

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

Title: Subscription status distribution behind orphan comped-delivery orders
Question: What subscription status do the 'orphan' Subscription-delivery-discount customers (no active subscription overlap found) actually have on record, since neither 'lapsed within 30 days' nor 'no subscription at all' explained them?
Grain: one row per subscription-status category (trialing, active, past_due, canceled, paused, incomplete, or 'no_subscription_record')
Columns:
- status category (customer's most recent subscription status for that tenant, by started_at, or 'no_subscription_record' if none exists)
- orphan customer count
- orphan order count (2025 'Subscription delivery (prepaid)' orders with no active-status date overlap, as defined in the prior report)
- % of all orphan orders
- avg days between subscription's ended_at (or current date if still open) and the orphan order's placed_at, where applicable
- total discount amount on these orders (EUR)
Details: Source: same orphan-order population as the prior report (order_discounts + discounts name='Subscription delivery (prepaid)' + orders placed_at in 2025, status != 'cancelled', with no overlapping subscription of status in ('trialing','active','past_due') per the prior definition). For each orphan order's customer (user_id, tenant_id), find their single most recent subscription record by started_at and report its status (not restricted to active ones this time) as the category, or 'no_subscription_record' if the customer has zero subscription rows for that tenant. Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Order by orphan order count descending. Print all categories (expect 5-8 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 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
  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 s
      WHERE s.tenant_id = o.tenant_id
        AND s.user_id = o.user_id
        AND s.status IN ('trialing','active','past_due')
        AND s.started_at <= o.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)
    )
),
latest_subscription AS (
  SELECT
    s.tenant_id AS tenant_id,
    s.user_id AS user_id,
    s.status AS status,
    s.ended_at AS ended_at,
    s.started_at AS started_at,
    ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM subscriptions s
),
orphan_with_category 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 AS discount_currency,
    COALESCE(ls.status, 'no_subscription_record') AS status_category,
    ls.ended_at AS sub_ended_at
  FROM orphan_orders oo
  LEFT JOIN latest_subscription ls
    ON ls.tenant_id = oo.tenant_id
   AND ls.user_id = oo.user_id
   AND ls.rn = 1
),
t0 AS (
  SELECT DISTINCT status_category FROM orphan_with_category
),
t1 AS (
  SELECT status_category, COUNT(DISTINCT tenant_id, user_id) AS orphan_customer_count
  FROM orphan_with_category
  GROUP BY status_category
),
t2 AS (
  SELECT status_category, COUNT(*) AS orphan_order_count
  FROM orphan_with_category
  GROUP BY status_category
),
t3 AS (
  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category
),
t4 AS (
  SELECT status_category,
         AVG(COALESCE(sub_ended_at::date, DATE '2026-01-05') - placed_at::date) AS avg_days_sub_end_to_order
  FROM orphan_with_category
  WHERE status_category <> 'no_subscription_record'
  GROUP BY status_category
),
t5 AS (
  SELECT ow.status_category,
         SUM(
           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount
                ELSE ow.discount_amount / er.rate
           END
         ) AS total_discount_eur
  FROM orphan_with_category ow
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ow.discount_currency
   AND er.as_of = ow.placed_at::date
  GROUP BY ow.status_category
)
SELECT
  t0.status_category AS status_category,
  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,
  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,
  ROUND(100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0), 2) AS pct_of_all_orphan_orders,
  ROUND(t4.avg_days_sub_end_to_order, 1) AS avg_days_sub_end_to_order,
  ROUND(COALESCE(t5.total_discount_eur, 0), 2) AS total_discount_amount_eur
FROM t0
LEFT JOIN t1 ON t1.status_category = t0.status_category
LEFT JOIN t2 ON t2.status_category = t0.status_category
CROSS JOIN t3
LEFT JOIN t4 ON t4.status_category = t0.status_category
LEFT JOIN t5 ON t5.status_category = t0.status_category
ORDER BY COALESCE(t2.orphan_order_count, 0) DESC, t0.status_category ASC

The warehouse answers

STATUS: REJECTED
  - does not compile: Binder Error: No function matches the given name and argument types 'count(BIGINT, BIGINT)'. You might need to add explicit type casts.

Bea calls dry_run

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
  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 s
      WHERE s.tenant_id = o.tenant_id
        AND s.user_id = o.user_id
        AND s.status IN ('trialing','active','past_due')
        AND s.started_at <= o.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)
    )
),
latest_subscription AS (
  SELECT
    s.tenant_id AS tenant_id,
    s.user_id AS user_id,
    s.status AS status,
    s.ended_at AS ended_at,
    s.started_at AS started_at,
    ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM subscriptions s
),
orphan_with_category 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 AS discount_currency,
    COALESCE(ls.status, 'no_subscription_record') AS status_category,
    ls.ended_at AS sub_ended_at
  FROM orphan_orders oo
  LEFT JOIN latest_subscription ls
    ON ls.tenant_id = oo.tenant_id
   AND ls.user_id = oo.user_id
   AND ls.rn = 1
),
t0 AS (
  SELECT DISTINCT status_category FROM orphan_with_category
),
t1 AS (
  SELECT status_category, COUNT(*) AS orphan_customer_count
  FROM (
    SELECT DISTINCT status_category, tenant_id, user_id
    FROM orphan_with_category
  ) dc
  GROUP BY status_category
),
t2 AS (
  SELECT status_category, COUNT(*) AS orphan_order_count
  FROM orphan_with_category
  GROUP BY status_category
),
t3 AS (
  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category
),
t4 AS (
  SELECT status_category,
         AVG(COALESCE(sub_ended_at::date, DATE '2026-01-05') - placed_at::date) AS avg_days_sub_end_to_order
  FROM orphan_with_category
  WHERE status_category <> 'no_subscription_record'
  GROUP BY status_category
),
t5 AS (
  SELECT ow.status_category,
         SUM(
           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount
                ELSE ow.discount_amount / er.rate
           END
         ) AS total_discount_eur
  FROM orphan_with_category ow
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ow.discount_currency
   AND er.as_of = ow.placed_at::date
  GROUP BY ow.status_category
)
SELECT
  t0.status_category AS status_category,
  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,
  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,
  ROUND(100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0), 2) AS pct_of_all_orphan_orders,
  ROUND(t4.avg_days_sub_end_to_order, 1) AS avg_days_sub_end_to_order,
  ROUND(COALESCE(t5.total_discount_eur, 0), 2) AS total_discount_amount_eur
FROM t0
LEFT JOIN t1 ON t1.status_category = t0.status_category
LEFT JOIN t2 ON t2.status_category = t0.status_category
CROSS JOIN t3
LEFT JOIN t4 ON t4.status_category = t0.status_category
LEFT JOIN t5 ON t5.status_category = t0.status_category
ORDER BY COALESCE(t2.orphan_order_count, 0) DESC, t0.status_category ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: status_category, orphan_customer_count, orphan_order_count, pct_of_all_orphan_orders, avg_days_sub_end_to_order, total_discount_amount_eur

BeaThe query compiles cleanly with exactly the requested columns. Submitting.

Bea calls submit

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
  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 s
      WHERE s.tenant_id = o.tenant_id
        AND s.user_id = o.user_id
        AND s.status IN ('trialing','active','past_due')
        AND s.started_at <= o.placed_at
        AND (s.ended_at IS NULL OR s.ended_at >= o.placed_at)
    )
),
latest_subscription AS (
  SELECT
    s.tenant_id AS tenant_id,
    s.user_id AS user_id,
    s.status AS status,
    s.ended_at AS ended_at,
    s.started_at AS started_at,
    ROW_NUMBER() OVER (PARTITION BY s.tenant_id, s.user_id ORDER BY s.started_at DESC, s.id DESC) AS rn
  FROM subscriptions s
),
orphan_with_category 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 AS discount_currency,
    COALESCE(ls.status, 'no_subscription_record') AS status_category,
    ls.ended_at AS sub_ended_at
  FROM orphan_orders oo
  LEFT JOIN latest_subscription ls
    ON ls.tenant_id = oo.tenant_id
   AND ls.user_id = oo.user_id
   AND ls.rn = 1
),
t0 AS (
  SELECT DISTINCT status_category FROM orphan_with_category
),
t1 AS (
  SELECT status_category, COUNT(*) AS orphan_customer_count
  FROM (
    SELECT DISTINCT status_category, tenant_id, user_id
    FROM orphan_with_category
  ) dc
  GROUP BY status_category
),
t2 AS (
  SELECT status_category, COUNT(*) AS orphan_order_count
  FROM orphan_with_category
  GROUP BY status_category
),
t3 AS (
  SELECT COUNT(*) AS total_orphan_orders FROM orphan_with_category
),
t4 AS (
  SELECT status_category,
         AVG(COALESCE(sub_ended_at::date, DATE '2026-01-05') - placed_at::date) AS avg_days_sub_end_to_order
  FROM orphan_with_category
  WHERE status_category <> 'no_subscription_record'
  GROUP BY status_category
),
t5 AS (
  SELECT ow.status_category,
         SUM(
           CASE WHEN ow.discount_currency = 'EUR' THEN ow.discount_amount
                ELSE ow.discount_amount / er.rate
           END
         ) AS total_discount_eur
  FROM orphan_with_category ow
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ow.discount_currency
   AND er.as_of = ow.placed_at::date
  GROUP BY ow.status_category
)
SELECT
  t0.status_category AS status_category,
  COALESCE(t1.orphan_customer_count, 0) AS orphan_customer_count,
  COALESCE(t2.orphan_order_count, 0) AS orphan_order_count,
  ROUND(100.0 * COALESCE(t2.orphan_order_count, 0) / NULLIF(t3.total_orphan_orders, 0), 2) AS pct_of_all_orphan_orders,
  ROUND(t4.avg_days_sub_end_to_order, 1) AS avg_days_sub_end_to_order,
  ROUND(COALESCE(t5.total_discount_eur, 0), 2) AS total_discount_amount_eur
FROM t0
LEFT JOIN t1 ON t1.status_category = t0.status_category
LEFT JOIN t2 ON t2.status_category = t0.status_category
CROSS JOIN t3
LEFT JOIN t4 ON t4.status_category = t0.status_category
LEFT JOIN t5 ON t5.status_category = t0.status_category
ORDER BY COALESCE(t2.orphan_order_count, 0) DESC, t0.status_category ASC

The warehouse answers

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

BeaSubmitted. Summary of what the report delivers: - **Grain:** one row per subscription-status category (trialing / active / past_due / canceled / paused / incomplete / no_subscription_record), derived from each orphan customer's single most recent subscription record (any status) by `started_at`. - **Orphan population:** orders with a 'Subscription delivery (prepaid)' discount, placed in 2025, status ≠ cancelled, and no subscription in ('trialing','active','past_due') whose date range covers the order's `placed_at` — matching the prior report's definition. - **Columns:** status category, orphan customer count, orphan order count, % of all orphan orders, avg days from subscription `ended_at` (or 2026-01-05 if still open) to the order's `placed_at` (null for no_subscription_record, since it doesn't apply), and total discount amount converted to EUR via same-day `exchange_rates`. - Ordered by orphan order count descending, tie-broken by category name for determinism. One judgment call noted in the submit note: since the exact "prior report" SQL wasn't available to me, I reconstructed the overlap/orphan logic directly from the definition given in this request.

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 (304 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (7 KB) · the night's reports (5 KB) · the week's memo (3 KB)

Subscription status distribution behind orphan comped-delivery orders

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 q157

Everything, as recorded