Queries · q154 · Head of Pricing and Promotions · week 1, night 3

Subscription-delivery discount orders with no overlapping active subscription

“Among the fully-comped 'Subscription delivery (prepaid)' orders, are the ones with no active subscription at the time concentrated in a few customers, or traceable to subscriptions that had just lapsed — or are they true unexplained giveaways?”

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

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

   Week 1; shared with Tobias Lindqvist, Chief Financial Officer

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

   One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

2. Marco Bianchi writes to Oliver Grant

   The manager, at the start of week 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

   Show the whole note
   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

3. Oliver Grant plans the night

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

   Night 2 sharpened three things:

   1. Report 1 confirmed 'Subscription delivery (prepaid)' orders are 100%-discounted (subtotal = discount_total exactly, shipping/tax = 0) — so it's not a data artifact, it's a deliberate full comp. But only 54-100% of these free orders overlap an active subscription (as low as 53.8% at Feather & Fin, 60.5% at Saffron & Sage). That means a meaningful share of "free delivery" orders have no active subscription on record at the time — either lapsed subscribers still getting shipped, or a real leakage pattern. This is squarely Tobias's "cost we haven't fully absorbed" question and needs a direct look before I call it fine.
   2. Report 2 came back with zero rows: no coupon in the warehouse has usage_limit or per_customer_limit set at all. That's a clean, if surprising, answer — there is no configured cap anywhere, so "breach" isn't the right abuse framing; the right framing is simply uncapped repeat use (which night 1's report already quantified). I'm treating this as settled and won't spend another slot re-asking it.
   3. Report 3's "lift" is confounded: it compares post-discount grand_total, so a percentage-off code mechanically shows a lower total even with an identical basket — that's why seasonal Sales show "negative lift" and Volume-tier discounts (which only trigger on already-large orders) show absurd 300-1000%+ "lift." I need to redo this on pre-discount subtotal (basket size) and keep structural volume-tier discounts separate from true marketing codes, or the read is misleading.

   Tonight's 3 reports: (A) drill into the subscription-delivery orders with no overlapping active subscription — concentration and whether it traces to lapsed subs or true giveaways; (B) a corrected incrementality read on true marketing discounts using pre-discount subtotal; (C) the still-untouched margin question — do discounted orders return/refund at a higher rate than baseline, eating further into margin. This covers the open pieces of Marco's and Tobias's questions before Thursday's sync.

   Read the whole plan
   Subscription-delivery discount orders with no overlapping active subscription

   Question

   Among the fully-comped 'Subscription delivery (prepaid)' orders, are the ones with no active subscription at the time concentrated in a few customers, or traceable to subscriptions that had just lapsed — or are they true unexplained giveaways?

   One row per

   tenant, restricted to 'Subscription delivery (prepaid)' discount orders in 2025 that have NO overlapping subscription with status in ('trialing','active','past_due')

   Columns

   • tenant name
   • orphan order count (no active subscription overlap)
   • orphan order count as % of all this discount's orders for the tenant
   • distinct customers among orphan orders
   • max orphan orders by a single customer
   • % of orphan customers whose most recent subscription has status 'canceled' and ended within 30 days before the order (lapsed-subscriber explanation)
   • % of orphan customers with no subscription record at all for this tenant (no explanation)
   • total discount amount on orphan orders (EUR)

4. Viktor 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 discount_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
     from order_discounts od
     join discounts d on d.id = od.discount_id and d.name = 'Subscription delivery (prepaid)'
     join orders o on o.id = od.order_id
     where o.placed_at >= timestamp '2025-01-01'
       and o.placed_at < timestamp '2026-01-01'
       and o.status <> 'cancelled'
   ),
   orphan_flagged AS (
     select
       do_.*,
       case when exists (
         select 1 from subscriptions s
         where s.tenant_id = do_.tenant_id
           and s.user_id = do_.user_id
           and s.started_at <= do_.placed_at
           and (s.ended_at is null or s.ended_at >= do_.placed_at)
           and s.status in ('trialing','active','past_due')
       ) then false else true end as is_orphan
     from discount_orders do_
   ),
   orphan_only AS (
     select * from orphan_flagged where is_orphan
   ),
   customer_orphan AS (
     select tenant_id, user_id, max(placed_at) as last_order_at, count(*) as orphan_order_count
     from orphan_only
     group by tenant_id, user_id
   ),
   subs_ranked AS (
     select
       s.tenant_id, s.user_id, s.status, s.ended_at, s.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
   ),
   most_recent_sub AS (
     select tenant_id, user_id, status, ended_at
     from subs_ranked
     where rn = 1
   ),
   customer_classified AS (
     select
       co.tenant_id,
       co.user_id,
       co.orphan_order_count,
       case
         when mrs.user_id is null then 'no_sub'
         when mrs.status = 'canceled' and mrs.ended_at is not null
           and mrs.ended_at >= co.last_order_at - interval '30 days'
           and mrs.ended_at <= co.last_order_at
         then 'lapsed'
         else 'other'
       end as classification
     from customer_orphan co
     left join most_recent_sub mrs
       on mrs.tenant_id = co.tenant_id and mrs.user_id = co.user_id
   ),
   orphan_eur AS (
     select
       oo.tenant_id,
       oo.order_id,
       oo.discount_amount / er.rate as discount_amount_eur
     from orphan_only oo
     join exchange_rates er
       on er.base_code = 'EUR'
       and er.quote_code = oo.currency_code
       and er.as_of = cast(oo.placed_at as date)
   ),
   tenant_totals AS (
     select tenant_id, count(*) as total_discount_orders
     from discount_orders
     group by tenant_id
   ),
   tenant_orphan AS (
     select
       tenant_id,
       count(*) as orphan_order_count,
       count(distinct user_id) as orphan_customers
     from orphan_only
     group by tenant_id
   ),
   tenant_max_customer AS (
     select tenant_id, max(orphan_order_count) as max_orphan_per_customer
     from customer_orphan
     group by tenant_id
   ),
   tenant_class AS (
     select
       tenant_id,
       count(*) as total_customers,
       sum(case when classification = 'lapsed' then 1 else 0 end) as lapsed_customers,
       sum(case when classification = 'no_sub' then 1 else 0 end) as no_sub_customers
     from customer_classified
     group by tenant_id
   ),
   tenant_eur AS (
     select tenant_id, sum(discount_amount_eur) as total_discount_eur
     from orphan_eur
     group by tenant_id
   )
   select
     t.name as tenant_name,
     tor.orphan_order_count,
     round(100.0 * tor.orphan_order_count / tt.total_discount_orders, 1) as orphan_pct_of_discount_orders,
     tor.orphan_customers as distinct_orphan_customers,
     tmc.max_orphan_per_customer,
     round(100.0 * tc.lapsed_customers / nullif(tc.total_customers,0), 1) as pct_lapsed_subscriber,
     round(100.0 * tc.no_sub_customers / nullif(tc.total_customers,0), 1) as pct_no_subscription_record,
     round(te.total_discount_eur, 2) as total_discount_amount_eur
   from tenant_orphan tor
   join tenants t on t.id = tor.tenant_id
   join tenant_totals tt on tt.tenant_id = tor.tenant_id
   join tenant_max_customer tmc on tmc.tenant_id = tor.tenant_id
   join tenant_class tc on tc.tenant_id = tor.tenant_id
   join tenant_eur te on te.tenant_id = tor.tenant_id
   order by tor.orphan_order_count desc, t.name asc

   “Query compiles and returns the expected 8 columns at tenant grain. 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 the approach: - Base set: orders joined to order_discounts/discounts for name = 'Subscription delivery (prepaid)', placed in 2025, status ≠ 'cancelled'. - Orphan flag: an order is orphan when no subscriptions row for the same tenant/user has started_at <= placed_at, (ended_at null or ≥ placed_at), and status in ('trialing','active','past_due'). - Customer classification: for each distinct orphan customer, found their single most-recent subscription record (any status, latest started_at) and compared it to their most recent orphan order date — 'lapsed' if canceled and ended within the preceding 30 days, 'no_sub' if they have no subscription row at all, otherwise left as unexplained (recoverable as the remainder). - Money: discount amounts converted to EUR via same-day exchange_rates (base EUR → order currency). Noted a judgment call in the submit note: since a customer can have multiple orphan orders, the lapse window uses that customer's most recent orphan-order date rather than a single order.”

5. The report comes back overnight

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

   tenant_name      | orphan_order_count | orphan_pct_of_discount_orders | distinct_orphan_customers | max_orphan_per_customer | pct_lapsed_subscriber | pct_no_subscription_record | total_discount_amount_eur
   -----------------+--------------------+-------------------------------+---------------------------+-------------------------+-----------------------+----------------------------+--------------------------
   Grind Collective |                167 |                          25.7 |                        28 |                      19 |                   0.0 |                        0.0 |                   9308.11
   Honeycomb Larder |                127 |                          35.9 |                        24 |                      21 |                   0.0 |                        0.0 |                   5978.91
   Kibblewick       |                100 |                          30.7 |                        21 |                      12 |                   0.0 |                        0.0 |                   3080.73
   Feather & Fin    |                 49 |                          46.2 |                        10 |                       8 |                   0.0 |                        0.0 |                   1796.63
   Saffron & Sage   |                 17 |                          39.5 |                         5 |                       8 |                   0.0 |                        0.0 |                   1974.96
   The Balm Room    |                  1 |                           6.3 |                         1 |                       1 |                   0.0 |                        0.0 |                    178.84
   (6 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q157Subscription status distribution behind orphan comped-delivery orders
   • q360Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025
   • q367Reconciled 2025 orphan population by tenant and subscription status (single pass)

   It builds on:

   • q151Order economics behind the 'Subscription delivery (prepaid)' discount

7. It becomes benchmark query q154

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

   • determinismtie-breaker: tenant names are not unique
   • 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 (16×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (5×)
   The benchmark text, against the submitted SQL

   WITH discount_orders AS (  select    o.id as order_id,    o.tenant_id,    o.user_id,    o.placed_at,    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    od.currency_code AS currency_code  from order_discounts od  join discounts d on d.id = od.discount_id and d.name = 'Subscription delivery (prepaid)'  join orders o on o.id = od.order_id  where o.placed_at >= timestamp '2025-01-01'    and o.placed_at < timestamp '2026-01-01'    and o.status <> 'cancelled'),orphan_flagged AS (  select    do_.*,    case when exists (      select 1 from subscriptions s      where s.tenant_id = do_.tenant_id        and s.user_id = do_.user_id        and s.started_at <= do_.placed_at        and (s.ended_at is null or s.ended_at >= do_.placed_at)        and s.status in ('trialing','active','past_due')    ) then false else true end as is_orphan  from discount_orders do_),orphan_only AS (  select * from orphan_flagged where is_orphan),customer_orphan AS (  select tenant_id, user_id, max(placed_at) as last_order_at, count(*) as orphan_order_count  from orphan_only  group by tenant_id, user_id),subs_ranked AS (  select    s.tenant_id, s.user_id, s.status, s.ended_at, s.started_at,    row_number() over (partition by s.tenant_id, s.user_id order by s.started_at desc, s.id desc) as rn    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),most_recent_sub AS (  select tenant_id, user_id, status, ended_at  from subs_ranked  where rn = 1),customer_classified AS (  select    co.tenant_id,    co.user_id,    co.orphan_order_count,    co.tenant_id AS tenant_id,    co.user_id AS user_id,    co.orphan_order_count AS orphan_order_count,    case      when mrs.user_id is null then 'no_sub'      when mrs.status = 'canceled' and mrs.ended_at is not null        and mrs.ended_at >= co.last_order_at - interval '30 days'        and mrs.ended_at <= co.last_order_at      then 'lapsed'      else 'other'    end as classification  from customer_orphan co  left join most_recent_sub mrs    on mrs.tenant_id = co.tenant_id and mrs.user_id = co.user_id),orphan_eur AS (  select    oo.tenant_id,    oo.order_id,    oo.discount_amount / er.rate as discount_amount_eur    oo.tenant_id AS tenant_id,    oo.order_id AS order_id,    CAST(oo.discount_amount / er.rate AS NUMERIC(18,6)) as discount_amount_eur  from orphan_only oo  join exchange_rates er    on er.base_code = 'EUR'    and er.quote_code = oo.currency_code    and er.as_of = cast(oo.placed_at as date)),tenant_totals AS (  select tenant_id, count(*) as total_discount_orders  from discount_orders  group by tenant_id),tenant_orphan AS (  select    tenant_id,    count(*) as orphan_order_count,    count(distinct user_id) as orphan_customers  from orphan_only  group by tenant_id),tenant_max_customer AS (  select tenant_id, max(orphan_order_count) as max_orphan_per_customer  from customer_orphan  group by tenant_id),tenant_class AS (  select    tenant_id,    count(*) as total_customers,    sum(case when classification = 'lapsed' then 1 else 0 end) as lapsed_customers,    sum(case when classification = 'no_sub' then 1 else 0 end) as no_sub_customers  from customer_classified  group by tenant_id),tenant_eur AS (  select tenant_id, sum(discount_amount_eur) as total_discount_eur  from orphan_eur  group by tenant_id)select  t.name as tenant_name,  tor.orphan_order_count,  round(100.0 * tor.orphan_order_count / tt.total_discount_orders, 1) as orphan_pct_of_discount_orders,  tor.orphan_order_count AS orphan_order_count,  100.0 * tor.orphan_order_count / tt.total_discount_orders as orphan_pct_of_discount_orders,  tor.orphan_customers as distinct_orphan_customers,  tmc.max_orphan_per_customer,  round(100.0 * tc.lapsed_customers / nullif(tc.total_customers,0), 1) as pct_lapsed_subscriber,  round(100.0 * tc.no_sub_customers / nullif(tc.total_customers,0), 1) as pct_no_subscription_record,  round(te.total_discount_eur, 2) as total_discount_amount_eur  tmc.max_orphan_per_customer AS max_orphan_per_customer,  100.0 * tc.lapsed_customers / nullif(tc.total_customers,0) as pct_lapsed_subscriber,  100.0 * tc.no_sub_customers / nullif(tc.total_customers,0) as pct_no_subscription_record,  te.total_discount_eur as total_discount_amount_eurfrom tenant_orphan torjoin tenants t on t.id = tor.tenant_idjoin tenant_totals tt on tt.tenant_id = tor.tenant_idjoin tenant_max_customer tmc on tmc.tenant_id = tor.tenant_idjoin tenant_class tc on tc.tenant_id = tor.tenant_idjoin tenant_eur te on te.tenant_id = tor.tenant_idorder by tor.orphan_order_count desc, t.name ascorder by tor.orphan_order_count desc NULLS FIRST, t.name asc NULLS LAST, t.id NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   WITH discount_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 currency_code
     from order_discounts od
     join discounts d on d.id = od.discount_id and d.name = 'Subscription delivery (prepaid)'
     join orders o on o.id = od.order_id
     where o.placed_at >= timestamp '2025-01-01'
       and o.placed_at < timestamp '2026-01-01'
       and o.status <> 'cancelled'
   ),
   orphan_flagged AS (
     select
       do_.*,
       case when exists (
         select 1 from subscriptions s
         where s.tenant_id = do_.tenant_id
           and s.user_id = do_.user_id
           and s.started_at <= do_.placed_at
           and (s.ended_at is null or s.ended_at >= do_.placed_at)
           and s.status in ('trialing','active','past_due')
       ) then false else true end as is_orphan
     from discount_orders do_
   ),
   orphan_only AS (
     select * from orphan_flagged where is_orphan
   ),
   customer_orphan AS (
     select tenant_id, user_id, max(placed_at) as last_order_at, count(*) as orphan_order_count
     from orphan_only
     group by tenant_id, user_id
   ),
   subs_ranked 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
   ),
   most_recent_sub AS (
     select tenant_id, user_id, status, ended_at
     from subs_ranked
     where rn = 1
   ),
   customer_classified AS (
     select
       co.tenant_id AS tenant_id,
       co.user_id AS user_id,
       co.orphan_order_count AS orphan_order_count,
       case
         when mrs.user_id is null then 'no_sub'
         when mrs.status = 'canceled' and mrs.ended_at is not null
           and mrs.ended_at >= co.last_order_at - interval '30 days'
           and mrs.ended_at <= co.last_order_at
         then 'lapsed'
         else 'other'
       end as classification
     from customer_orphan co
     left join most_recent_sub mrs
       on mrs.tenant_id = co.tenant_id and mrs.user_id = co.user_id
   ),
   orphan_eur AS (
     select
       oo.tenant_id AS tenant_id,
       oo.order_id AS order_id,
       CAST(oo.discount_amount / er.rate AS NUMERIC(18,6)) as discount_amount_eur
     from orphan_only oo
     join exchange_rates er
       on er.base_code = 'EUR'
       and er.quote_code = oo.currency_code
       and er.as_of = cast(oo.placed_at as date)
   ),
   tenant_totals AS (
     select tenant_id, count(*) as total_discount_orders
     from discount_orders
     group by tenant_id
   ),
   tenant_orphan AS (
     select
       tenant_id,
       count(*) as orphan_order_count,
       count(distinct user_id) as orphan_customers
     from orphan_only
     group by tenant_id
   ),
   tenant_max_customer AS (
     select tenant_id, max(orphan_order_count) as max_orphan_per_customer
     from customer_orphan
     group by tenant_id
   ),
   tenant_class AS (
     select
       tenant_id,
       count(*) as total_customers,
       sum(case when classification = 'lapsed' then 1 else 0 end) as lapsed_customers,
       sum(case when classification = 'no_sub' then 1 else 0 end) as no_sub_customers
     from customer_classified
     group by tenant_id
   ),
   tenant_eur AS (
     select tenant_id, sum(discount_amount_eur) as total_discount_eur
     from orphan_eur
     group by tenant_id
   )
   select
     t.name as tenant_name,
     tor.orphan_order_count AS orphan_order_count,
     100.0 * tor.orphan_order_count / tt.total_discount_orders as orphan_pct_of_discount_orders,
     tor.orphan_customers as distinct_orphan_customers,
     tmc.max_orphan_per_customer AS max_orphan_per_customer,
     100.0 * tc.lapsed_customers / nullif(tc.total_customers,0) as pct_lapsed_subscriber,
     100.0 * tc.no_sub_customers / nullif(tc.total_customers,0) as pct_no_subscription_record,
     te.total_discount_eur as total_discount_amount_eur
   from tenant_orphan tor
   join tenants t on t.id = tor.tenant_id
   join tenant_totals tt on tt.tenant_id = tor.tenant_id
   join tenant_max_customer tmc on tmc.tenant_id = tor.tenant_id
   join tenant_class tc on tc.tenant_id = tor.tenant_id
   join tenant_eur te on te.tenant_id = tor.tenant_id
   order by tor.orphan_order_count desc NULLS FIRST, t.name asc NULLS LAST, t.id NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 69, Column 29: 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": "eb3a3769-641c-48ff-a504-91cf0ded3b55", "query_lab
   • DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: true })
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table45.discount_amount Decimal(14, 4) Decimal64(size = 0), __table54.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

   Scale factor 1000, c8a.24xlarge, after the last load. q154 on the start page, with every configuration.

8. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Viktor's whole session, 7 turns

   • The request, as Viktor received it

     Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):
     
     Title: Subscription-delivery discount orders with no overlapping active subscription
     Question: Among the fully-comped 'Subscription delivery (prepaid)' orders, are the ones with no active subscription at the time concentrated in a few customers, or traceable to subscriptions that had just lapsed — or are they true unexplained giveaways?
     Grain: one row per tenant, restricted to 'Subscription delivery (prepaid)' discount orders in 2025 that have NO overlapping subscription with status in ('trialing','active','past_due')
     Columns:
     - tenant name
     - orphan order count (no active subscription overlap)
     - orphan order count as % of all this discount's orders for the tenant
     - distinct customers among orphan orders
     - max orphan orders by a single customer
     - % of orphan customers whose most recent subscription has status 'canceled' and ended within 30 days before the order (lapsed-subscriber explanation)
     - % of orphan customers with no subscription record at all for this tenant (no explanation)
     - total discount amount on orphan orders (EUR)
     Details: Source: order_discounts + discounts (name = 'Subscription delivery (prepaid)') + orders (placed_at in 2025-01-01 to 2025-12-31, status != 'cancelled', user_id, tenant_id) + tenants. For each such order, determine subscription overlap via subscriptions (same user_id, tenant_id) where started_at <= placed_at and (ended_at is null or ended_at >= placed_at) and status in ('trialing','active','past_due'); orders with no such matching subscription are 'orphan'. For orphan orders, also check each customer's most recent subscription record (by started_at) regardless of status to classify as 'lapsed within 30 days' (status='canceled' and ended_at is not null and ended_at between placed_at - interval 30 days and placed_at) vs 'no subscription record at all'. 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, tenant name as tie-break. Print all tenants (expect ~8, matching the prior report).
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Viktor calls dry_run

     WITH discount_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
       from order_discounts od
       join discounts d on d.id = od.discount_id and d.name = 'Subscription delivery (prepaid)'
       join orders o on o.id = od.order_id
       where o.placed_at >= timestamp '2025-01-01'
         and o.placed_at < timestamp '2026-01-01'
         and o.status <> 'cancelled'
     ),
     orphan_flagged AS (
       select
         do_.*,
         case when exists (
           select 1 from subscriptions s
           where s.tenant_id = do_.tenant_id
             and s.user_id = do_.user_id
             and s.started_at <= do_.placed_at
             and (s.ended_at is null or s.ended_at >= do_.placed_at)
             and s.status in ('trialing','active','past_due')
         ) then false else true end as is_orphan
       from discount_orders do_
     ),
     orphan_only AS (
       select * from orphan_flagged where is_orphan
     ),
     customer_orphan AS (
       select tenant_id, user_id, max(placed_at) as last_order_at, count(*) as orphan_order_count
       from orphan_only
       group by tenant_id, user_id
     ),
     subs_ranked AS (
       select
         s.tenant_id, s.user_id, s.status, s.ended_at, s.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
     ),
     most_recent_sub AS (
       select tenant_id, user_id, status, ended_at
       from subs_ranked
       where rn = 1
     ),
     customer_classified AS (
       select
         co.tenant_id,
         co.user_id,
         co.orphan_order_count,
         case
           when mrs.user_id is null then 'no_sub'
           when mrs.status = 'canceled' and mrs.ended_at is not null
             and mrs.ended_at >= co.last_order_at - interval '30 days'
             and mrs.ended_at <= co.last_order_at
           then 'lapsed'
           else 'other'
         end as classification
       from customer_orphan co
       left join most_recent_sub mrs
         on mrs.tenant_id = co.tenant_id and mrs.user_id = co.user_id
     ),
     orphan_eur AS (
       select
         oo.tenant_id,
         oo.order_id,
         oo.discount_amount / er.rate as discount_amount_eur
       from orphan_only oo
       join exchange_rates er
         on er.base_code = 'EUR'
         and er.quote_code = oo.currency_code
         and er.as_of = cast(oo.placed_at as date)
     ),
     tenant_totals AS (
       select tenant_id, count(*) as total_discount_orders
       from discount_orders
       group by tenant_id
     ),
     tenant_orphan AS (
       select
         tenant_id,
         count(*) as orphan_order_count,
         count(distinct user_id) as orphan_customers
       from orphan_only
       group by tenant_id
     ),
     tenant_max_customer AS (
       select tenant_id, max(orphan_order_count) as max_orphan_per_customer
       from customer_orphan
       group by tenant_id
     ),
     tenant_class AS (
       select
         tenant_id,
         count(*) as total_customers,
         sum(case when classification = 'lapsed' then 1 else 0 end) as lapsed_customers,
         sum(case when classification = 'no_sub' then 1 else 0 end) as no_sub_customers
       from customer_classified
       group by tenant_id
     ),
     tenant_eur AS (
       select tenant_id, sum(discount_amount_eur) as total_discount_eur
       from orphan_eur
       group by tenant_id
     )
     select
       t.name as tenant_name,
       tor.orphan_order_count,
       round(100.0 * tor.orphan_order_count / tt.total_discount_orders, 1) as orphan_pct_of_discount_orders,
       tor.orphan_customers as distinct_orphan_customers,
       tmc.max_orphan_per_customer,
       round(100.0 * tc.lapsed_customers / nullif(tc.total_customers,0), 1) as pct_lapsed_subscriber,
       round(100.0 * tc.no_sub_customers / nullif(tc.total_customers,0), 1) as pct_no_subscription_record,
       round(te.total_discount_eur, 2) as total_discount_amount_eur
     from tenant_orphan tor
     join tenants t on t.id = tor.tenant_id
     join tenant_totals tt on tt.tenant_id = tor.tenant_id
     join tenant_max_customer tmc on tmc.tenant_id = tor.tenant_id
     join tenant_class tc on tc.tenant_id = tor.tenant_id
     join tenant_eur te on te.tenant_id = tor.tenant_id
     order by tor.orphan_order_count desc, t.name asc

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: tenant_name, orphan_order_count, orphan_pct_of_discount_orders, distinct_orphan_customers, max_orphan_per_customer, pct_lapsed_subscriber, pct_no_subscription_record, total_discount_amount_eur

   • ViktorQuery compiles and returns the expected 8 columns at tenant grain. Submitting.
   • Viktor calls submit

     WITH discount_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
       from order_discounts od
       join discounts d on d.id = od.discount_id and d.name = 'Subscription delivery (prepaid)'
       join orders o on o.id = od.order_id
       where o.placed_at >= timestamp '2025-01-01'
         and o.placed_at < timestamp '2026-01-01'
         and o.status <> 'cancelled'
     ),
     orphan_flagged AS (
       select
         do_.*,
         case when exists (
           select 1 from subscriptions s
           where s.tenant_id = do_.tenant_id
             and s.user_id = do_.user_id
             and s.started_at <= do_.placed_at
             and (s.ended_at is null or s.ended_at >= do_.placed_at)
             and s.status in ('trialing','active','past_due')
         ) then false else true end as is_orphan
       from discount_orders do_
     ),
     orphan_only AS (
       select * from orphan_flagged where is_orphan
     ),
     customer_orphan AS (
       select tenant_id, user_id, max(placed_at) as last_order_at, count(*) as orphan_order_count
       from orphan_only
       group by tenant_id, user_id
     ),
     subs_ranked AS (
       select
         s.tenant_id, s.user_id, s.status, s.ended_at, s.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
     ),
     most_recent_sub AS (
       select tenant_id, user_id, status, ended_at
       from subs_ranked
       where rn = 1
     ),
     customer_classified AS (
       select
         co.tenant_id,
         co.user_id,
         co.orphan_order_count,
         case
           when mrs.user_id is null then 'no_sub'
           when mrs.status = 'canceled' and mrs.ended_at is not null
             and mrs.ended_at >= co.last_order_at - interval '30 days'
             and mrs.ended_at <= co.last_order_at
           then 'lapsed'
           else 'other'
         end as classification
       from customer_orphan co
       left join most_recent_sub mrs
         on mrs.tenant_id = co.tenant_id and mrs.user_id = co.user_id
     ),
     orphan_eur AS (
       select
         oo.tenant_id,
         oo.order_id,
         oo.discount_amount / er.rate as discount_amount_eur
       from orphan_only oo
       join exchange_rates er
         on er.base_code = 'EUR'
         and er.quote_code = oo.currency_code
         and er.as_of = cast(oo.placed_at as date)
     ),
     tenant_totals AS (
       select tenant_id, count(*) as total_discount_orders
       from discount_orders
       group by tenant_id
     ),
     tenant_orphan AS (
       select
         tenant_id,
         count(*) as orphan_order_count,
         count(distinct user_id) as orphan_customers
       from orphan_only
       group by tenant_id
     ),
     tenant_max_customer AS (
       select tenant_id, max(orphan_order_count) as max_orphan_per_customer
       from customer_orphan
       group by tenant_id
     ),
     tenant_class AS (
       select
         tenant_id,
         count(*) as total_customers,
         sum(case when classification = 'lapsed' then 1 else 0 end) as lapsed_customers,
         sum(case when classification = 'no_sub' then 1 else 0 end) as no_sub_customers
       from customer_classified
       group by tenant_id
     ),
     tenant_eur AS (
       select tenant_id, sum(discount_amount_eur) as total_discount_eur
       from orphan_eur
       group by tenant_id
     )
     select
       t.name as tenant_name,
       tor.orphan_order_count,
       round(100.0 * tor.orphan_order_count / tt.total_discount_orders, 1) as orphan_pct_of_discount_orders,
       tor.orphan_customers as distinct_orphan_customers,
       tmc.max_orphan_per_customer,
       round(100.0 * tc.lapsed_customers / nullif(tc.total_customers,0), 1) as pct_lapsed_subscriber,
       round(100.0 * tc.no_sub_customers / nullif(tc.total_customers,0), 1) as pct_no_subscription_record,
       round(te.total_discount_eur, 2) as total_discount_amount_eur
     from tenant_orphan tor
     join tenants t on t.id = tor.tenant_id
     join tenant_totals tt on tt.tenant_id = tor.tenant_id
     join tenant_max_customer tmc on tmc.tenant_id = tor.tenant_id
     join tenant_class tc on tc.tenant_id = tor.tenant_id
     join tenant_eur te on te.tenant_id = tor.tenant_id
     order by tor.orphan_order_count desc, t.name asc

   • The warehouse answers

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

   • ViktorSubmitted. Summary of the approach: - **Base set**: orders joined to `order_discounts`/`discounts` for name = 'Subscription delivery (prepaid)', placed in 2025, status ≠ 'cancelled'. - **Orphan flag**: an order is orphan when no `subscriptions` row for the same tenant/user has `started_at <= placed_at`, (`ended_at` null or ≥ `placed_at`), and status in ('trialing','active','past_due'). - **Customer classification**: for each distinct orphan customer, found their single most-recent subscription record (any status, latest `started_at`) and compared it to their most recent orphan order date — 'lapsed' if canceled and ended within the preceding 30 days, 'no_sub' if they have no subscription row at all, otherwise left as unexplained (recoverable as the remainder). - **Money**: discount amounts converted to EUR via same-day `exchange_rates` (base EUR → order currency). Noted a judgment call in the submit note: since a customer can have multiple orphan orders, the lapse window uses that customer's most recent orphan-order date rather than a single order.

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

   Viktor's transcript (297 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (8 KB) · the night's reports (20 KB) · the week's memo (3 KB)