Queries · q365 · Head of Pricing and Promotions · week 2, night 3

Root cause of the 291 'unexplained' active-bucket orphan orders

“For the 291 orphan orders whose customer's most-recent subscription is 'active', does that subscription's own start/end dates show it started after the order, show it marked active while already past its end date, or show dates that should have overlapped (meaning the original orphan flag was a bug)?”

Manager · asked the questionMarco BianchiHead of Pricing and Promotions. Previously in revenue management at a travel company.

Analyst · planned the reportOliver GrantPeer-minded: compares each merchant with peers of similar size and vertical.

Engineer · wrote the SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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

A business event lands on the Head of Pricing and Promotions's desk
Week 1; shared with Tobias Lindqvist, Chief Financial Officer
Finance questions the cost of discounts. Which promotions and coupons brought additional sales, which only gave away margin, and is anything being misused?
One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events
Marco Bianchi writes to Oliver Grant
The manager, at the start of week 2
Subject: Good work — here's where I want to go next

Oliver,

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

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

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

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

Thanks, Marco
Oliver Grant plans the night
The analyst · week 2, night 3, request 1 of 2
Night 2's categorization fix produced a clean but puzzling result: all 291 active-bucket orders now land in a single "unexplained" category (100%, €11,746.39) — meaning none of them precede a first subscription and none fall between two stints. Logically that leaves only one explanation: the "active" subscription record itself doesn't actually cover the order date (otherwise the order wouldn't have been flagged as orphan in the first place), which most likely means either (a) that subscription started after the free order — a different, more benign situation than leakage — or (b) the subscription is marked status='active' but already has a past ended_at on it, a genuine data inconsistency where the status field disagrees with the subscription's own dates. I need to split these apart with a precise, narrow query before I can tell Marco whether this €11.7k belongs in the leakage total, is a separate curiosity, or signals a real bug in the original orphan flag.

The good news from night 2: the incrementality re-cut (matched by item count) shows positive lift in every single bucket (+13% to +117%), which is a much stronger, more defensible signal than last week's confounded numbers — I'm now reasonably confident the top discounts are associated with genuinely bigger baskets, not just selection into already-large carts. And the tenant-by-half-year leakage breakdown confirms the 2024-peak/2025-decline pattern is broad-based across all 6 tenants, not one outlier fixing itself — so "slow-burning historical gap, now resolving" is a solid, well-checked claim for the memo. Neither needs more digging.

Tonight's two priorities: (1) cleanly split the 291 "unexplained" active-bucket orders into their true underlying cause (started-after-order vs. active-status-but-already-ended vs. a genuine bug in the original orphan flag), which determines the final €-figure I give Tobias; (2) check whether "subscription marked active/trialing/past_due but already has a past ended_at" is a one-off to these 6 tenants or a platform-wide data-quality issue, since that changes how I'd frame the finding and who else should know about it. I'm holding back a third slot rather than manufacturing a question — the other open items (incrementality, trend concentration, Welcome10 drivers) are already well-answered and I'd rather save the slot for whatever the diagnostic turns up.
Root cause of the 291 'unexplained' active-bucket orphan orders
Question
For the 291 orphan orders whose customer's most-recent subscription is 'active', does that subscription's own start/end dates show it started after the order, show it marked active while already past its end date, or show dates that should have overlapped (meaning the original orphan flag was a bug)?
One row per
explanation category, covering the full 291-order population
Columns
- category: 'subscription started after this order' (S.started_at > order.placed_at) / 'active status but already past its end date' (S.started_at <= placed_at AND S.ended_at is not null AND S.ended_at < placed_at) / 'dates indicate overlap - likely a bug in the original orphan flag' (S.started_at <= placed_at AND (S.ended_at is null OR S.ended_at >= placed_at))
- order count
- % of the 291-order bucket
- distinct customers
- total discount amount (EUR)

Omar writes the SQL

The engineer · 1 dry run on the empty schema, then the submission

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

WITH matching_discounts AS (
  SELECT od.order_id, od.tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
  GROUP BY od.order_id, od.tenant_id
),
orphan_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.placed_at,
    s.started_at AS sub_started_at,
    s.ended_at AS sub_ended_at,
    md.total_discount_amount,
    md.currency_code
  FROM orders o
  JOIN matching_discounts md ON md.order_id = o.id AND md.tenant_id = o.tenant_id
  JOIN LATERAL (
    SELECT sub.*
    FROM subscriptions sub
    WHERE sub.user_id = o.user_id AND sub.tenant_id = o.tenant_id
    ORDER BY sub.started_at DESC
    LIMIT 1
  ) s ON true
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND s.status = 'active'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions sub2
      WHERE sub2.user_id = o.user_id AND sub2.tenant_id = o.tenant_id
        AND sub2.status IN ('active','trialing','past_due')
        AND sub2.started_at <= o.placed_at
        AND (sub2.ended_at IS NULL OR sub2.ended_at >= o.placed_at)
    )
),
classified AS (
  SELECT
    oo.*,
    CASE
      WHEN sub_started_at > placed_at THEN 'subscription started after this order'
      WHEN sub_started_at <= placed_at AND sub_ended_at IS NOT NULL AND sub_ended_at < placed_at THEN 'active status but already past its end date'
      ELSE 'dates indicate overlap - likely a bug in the original orphan flag'
    END AS category,
    total_discount_amount / er.rate AS discount_amount_eur
  FROM orphan_orders oo
  LEFT 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)
)
SELECT
  category,
  COUNT(*) AS order_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_bucket,
  COUNT(DISTINCT user_id) AS distinct_customers,
  ROUND(SUM(discount_amount_eur), 2) AS total_discount_eur,
  SUM(COUNT(*)) OVER () AS check_total_291
FROM classified
GROUP BY category
ORDER BY order_count DESC, category

“Query compiles and returns the expected 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 orphan-order population into the three mutually-exclusive date-comparison categories, with order count, % share, distinct customers, and EUR-converted discount totals per category, plus a running total column to verify the 291 check.”

The report comes back overnight

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

category                              | order_count | pct_of_bucket | distinct_customers | total_discount_eur | check_total_291
--------------------------------------+-------------+---------------+--------------------+--------------------+----------------
subscription started after this order |         291 |         100.0 |                 60 |            3363.71 |             291
(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:
- q366Platform-wide check: how often do 'currently active' subscriptions already have a past end date?
- q369Fan-out-safe date classification of the 2025 active-bucket orphan orders
It builds on:
- q362Corrected categorization of all 291 active-bucket orphan subscription-delivery orders

It becomes benchmark query q365

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

spellingfetch first
determinismtie-breaker in the LATERAL FETCH 1: subscriptions.id
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 (7×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
placeholderfetch first

The benchmark text, against the submitted SQL

WITH matching_discounts AS (  SELECT od.order_id, od.tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code  SELECT od.order_id AS order_id, od.tenant_id AS tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code  FROM order_discounts od  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id  WHERE d.name = 'Subscription delivery (prepaid)'  GROUP BY od.order_id, od.tenant_id),orphan_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,    s.started_at AS sub_started_at,    s.ended_at AS sub_ended_at,    md.total_discount_amount,    md.currency_code    md.total_discount_amount AS total_discount_amount,    md.currency_code AS currency_code  FROM orders o  JOIN matching_discounts md ON md.order_id = o.id AND md.tenant_id = o.tenant_id  JOIN LATERAL (    SELECT sub.*    FROM subscriptions sub    WHERE sub.user_id = o.user_id AND sub.tenant_id = o.tenant_id    ORDER BY sub.started_at DESC    LIMIT 1    ORDER BY sub.started_at DESC NULLS FIRST, sub.id DESC NULLS FIRST    {{fetch_first(1)}}  ) s ON true  WHERE o.status <> 'cancelled'    AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'    AND s.status = 'active'    AND NOT EXISTS (      SELECT 1 FROM subscriptions sub2      WHERE sub2.user_id = o.user_id AND sub2.tenant_id = o.tenant_id        AND sub2.status IN ('active','trialing','past_due')        AND sub2.started_at <= o.placed_at        AND (sub2.ended_at IS NULL OR sub2.ended_at >= o.placed_at)    )),classified AS (  SELECT    oo.*,    CASE      WHEN sub_started_at > placed_at THEN 'subscription started after this order'      WHEN sub_started_at <= placed_at AND sub_ended_at IS NOT NULL AND sub_ended_at < placed_at THEN 'active status but already past its end date'      ELSE 'dates indicate overlap - likely a bug in the original orphan flag'    END AS category,    total_discount_amount / er.rate AS discount_amount_eur    CAST(total_discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur  FROM orphan_orders oo  LEFT 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))SELECT  category,  COUNT(*) AS order_count,  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_bucket,  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_bucket,  COUNT(DISTINCT user_id) AS distinct_customers,  ROUND(SUM(discount_amount_eur), 2) AS total_discount_eur,  SUM(discount_amount_eur) AS total_discount_eur,  SUM(COUNT(*)) OVER () AS check_total_291FROM classifiedGROUP BY categoryORDER BY order_count DESC, categoryORDER BY order_count DESC NULLS FIRST, category NULLS LAST

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

WITH matching_discounts AS (
  SELECT od.order_id AS order_id, od.tenant_id AS tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
  GROUP BY od.order_id, od.tenant_id
),
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,
    s.started_at AS sub_started_at,
    s.ended_at AS sub_ended_at,
    md.total_discount_amount AS total_discount_amount,
    md.currency_code AS currency_code
  FROM orders o
  JOIN matching_discounts md ON md.order_id = o.id AND md.tenant_id = o.tenant_id
  JOIN LATERAL (
    SELECT sub.*
    FROM subscriptions sub
    WHERE sub.user_id = o.user_id AND sub.tenant_id = o.tenant_id
    ORDER BY sub.started_at DESC NULLS FIRST, sub.id DESC NULLS FIRST
    FETCH FIRST 1 ROWS ONLY
  ) s ON true
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND s.status = 'active'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions sub2
      WHERE sub2.user_id = o.user_id AND sub2.tenant_id = o.tenant_id
        AND sub2.status IN ('active','trialing','past_due')
        AND sub2.started_at <= o.placed_at
        AND (sub2.ended_at IS NULL OR sub2.ended_at >= o.placed_at)
    )
),
classified AS (
  SELECT
    oo.*,
    CASE
      WHEN sub_started_at > placed_at THEN 'subscription started after this order'
      WHEN sub_started_at <= placed_at AND sub_ended_at IS NOT NULL AND sub_ended_at < placed_at THEN 'active status but already past its end date'
      ELSE 'dates indicate overlap - likely a bug in the original orphan flag'
    END AS category,
    CAST(total_discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur
  FROM orphan_orders oo
  LEFT 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)
)
SELECT
  category,
  COUNT(*) AS order_count,
  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_bucket,
  COUNT(DISTINCT user_id) AS distinct_customers,
  SUM(discount_amount_eur) AS total_discount_eur,
  SUM(COUNT(*)) OVER () AS check_total_291
FROM classified
GROUP BY category
ORDER BY order_count DESC NULLS FIRST, category NULLS LAST

WITH matching_discounts AS (
  SELECT od.order_id AS order_id, od.tenant_id AS tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
  GROUP BY od.order_id, od.tenant_id
),
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,
    s.started_at AS sub_started_at,
    s.ended_at AS sub_ended_at,
    md.total_discount_amount AS total_discount_amount,
    md.currency_code AS currency_code
  FROM orders o
  JOIN matching_discounts md ON md.order_id = o.id AND md.tenant_id = o.tenant_id
  JOIN LATERAL (
    SELECT sub.*
    FROM subscriptions sub
    WHERE sub.user_id = o.user_id AND sub.tenant_id = o.tenant_id
    ORDER BY sub.started_at DESC NULLS FIRST, sub.id DESC NULLS FIRST
    LIMIT 1
  ) s ON true
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND s.status = 'active'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions sub2
      WHERE sub2.user_id = o.user_id AND sub2.tenant_id = o.tenant_id
        AND sub2.status IN ('active','trialing','past_due')
        AND sub2.started_at <= o.placed_at
        AND (sub2.ended_at IS NULL OR sub2.ended_at >= o.placed_at)
    )
),
classified AS (
  SELECT
    oo.*,
    CASE
      WHEN sub_started_at > placed_at THEN 'subscription started after this order'
      WHEN sub_started_at <= placed_at AND sub_ended_at IS NOT NULL AND sub_ended_at < placed_at THEN 'active status but already past its end date'
      ELSE 'dates indicate overlap - likely a bug in the original orphan flag'
    END AS category,
    CAST(total_discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur
  FROM orphan_orders oo
  LEFT 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)
)
SELECT
  category,
  COUNT(*) AS order_count,
  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_bucket,
  COUNT(DISTINCT user_id) AS distinct_customers,
  SUM(discount_amount_eur) AS total_discount_eur,
  SUM(COUNT(*)) OVER () AS check_total_291
FROM classified
GROUP BY category
ORDER BY order_count DESC NULLS FIRST, category NULLS LAST

Firebolt{ "errors": [ { "description": "Line 46, Column 32: 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": "11ef1d40-68a5-4261-8674-d9f310cec231", "query_lab
DataFusionThis feature is not implemented: Physical plan does not support logical expression OuterReferenceColumn(Field { name: "user_id", data_type: Int64 }, Column { relation: Some(Bare { table: "o" }), name: "user_id" })
ClickHouseCode: 62. DB::Exception: Syntax error: failed at position 786 (sub) (line 21, col 12): sub.*

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

Everything, as recorded

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

Omar's whole session, 7 turns

The request, as Omar received it

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

Title: Root cause of the 291 'unexplained' active-bucket orphan orders
Question: For the 291 orphan orders whose customer's most-recent subscription is 'active', does that subscription's own start/end dates show it started after the order, show it marked active while already past its end date, or show dates that should have overlapped (meaning the original orphan flag was a bug)?
Grain: one row per explanation category, covering the full 291-order population
Columns:
- category: 'subscription started after this order' (S.started_at > order.placed_at) / 'active status but already past its end date' (S.started_at <= placed_at AND S.ended_at is not null AND S.ended_at < placed_at) / 'dates indicate overlap - likely a bug in the original orphan flag' (S.started_at <= placed_at AND (S.ended_at is null OR S.ended_at >= placed_at))
- order count
- % of the 291-order bucket
- distinct customers
- total discount amount (EUR)
Details: Source: the exact same 291 orders from last week's report (order_discounts + discounts name='Subscription delivery (prepaid)' + orders placed_at 2025, status != 'cancelled', no overlapping active/trialing/past_due subscription, most-recent subscription by started_at has status='active'). For each order, use S = that same most-recent-by-started_at subscription row (not a different join), and classify purely by comparing S.started_at and S.ended_at to order.placed_at per the three mutually-exclusive, exhaustive categories above. Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Add an explicit check that the three category counts sum to 291. Order by order count descending. Print all categories.

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

Omar calls dry_run

WITH matching_discounts AS (
  SELECT od.order_id, od.tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
  GROUP BY od.order_id, od.tenant_id
),
orphan_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.placed_at,
    s.started_at AS sub_started_at,
    s.ended_at AS sub_ended_at,
    md.total_discount_amount,
    md.currency_code
  FROM orders o
  JOIN matching_discounts md ON md.order_id = o.id AND md.tenant_id = o.tenant_id
  JOIN LATERAL (
    SELECT sub.*
    FROM subscriptions sub
    WHERE sub.user_id = o.user_id AND sub.tenant_id = o.tenant_id
    ORDER BY sub.started_at DESC
    LIMIT 1
  ) s ON true
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND s.status = 'active'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions sub2
      WHERE sub2.user_id = o.user_id AND sub2.tenant_id = o.tenant_id
        AND sub2.status IN ('active','trialing','past_due')
        AND sub2.started_at <= o.placed_at
        AND (sub2.ended_at IS NULL OR sub2.ended_at >= o.placed_at)
    )
),
classified AS (
  SELECT
    oo.*,
    CASE
      WHEN sub_started_at > placed_at THEN 'subscription started after this order'
      WHEN sub_started_at <= placed_at AND sub_ended_at IS NOT NULL AND sub_ended_at < placed_at THEN 'active status but already past its end date'
      ELSE 'dates indicate overlap - likely a bug in the original orphan flag'
    END AS category,
    total_discount_amount / er.rate AS discount_amount_eur
  FROM orphan_orders oo
  LEFT 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)
)
SELECT
  category,
  COUNT(*) AS order_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_bucket,
  COUNT(DISTINCT user_id) AS distinct_customers,
  ROUND(SUM(discount_amount_eur), 2) AS total_discount_eur,
  SUM(COUNT(*)) OVER () AS check_total_291
FROM classified
GROUP BY category
ORDER BY order_count DESC, category

The warehouse answers

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

OmarQuery compiles and returns the expected columns. Submitting.

Omar calls submit

WITH matching_discounts AS (
  SELECT od.order_id, od.tenant_id, SUM(od.amount) AS total_discount_amount, MAX(od.currency_code) AS currency_code
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
  GROUP BY od.order_id, od.tenant_id
),
orphan_orders AS (
  SELECT
    o.id AS order_id,
    o.tenant_id,
    o.user_id,
    o.placed_at,
    s.started_at AS sub_started_at,
    s.ended_at AS sub_ended_at,
    md.total_discount_amount,
    md.currency_code
  FROM orders o
  JOIN matching_discounts md ON md.order_id = o.id AND md.tenant_id = o.tenant_id
  JOIN LATERAL (
    SELECT sub.*
    FROM subscriptions sub
    WHERE sub.user_id = o.user_id AND sub.tenant_id = o.tenant_id
    ORDER BY sub.started_at DESC
    LIMIT 1
  ) s ON true
  WHERE o.status <> 'cancelled'
    AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
    AND s.status = 'active'
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions sub2
      WHERE sub2.user_id = o.user_id AND sub2.tenant_id = o.tenant_id
        AND sub2.status IN ('active','trialing','past_due')
        AND sub2.started_at <= o.placed_at
        AND (sub2.ended_at IS NULL OR sub2.ended_at >= o.placed_at)
    )
),
classified AS (
  SELECT
    oo.*,
    CASE
      WHEN sub_started_at > placed_at THEN 'subscription started after this order'
      WHEN sub_started_at <= placed_at AND sub_ended_at IS NOT NULL AND sub_ended_at < placed_at THEN 'active status but already past its end date'
      ELSE 'dates indicate overlap - likely a bug in the original orphan flag'
    END AS category,
    total_discount_amount / er.rate AS discount_amount_eur
  FROM orphan_orders oo
  LEFT 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)
)
SELECT
  category,
  COUNT(*) AS order_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_bucket,
  COUNT(DISTINCT user_id) AS distinct_customers,
  ROUND(SUM(discount_amount_eur), 2) AS total_discount_eur,
  SUM(COUNT(*)) OVER () AS check_total_291
FROM classified
GROUP BY category
ORDER BY order_count DESC, category

The warehouse answers

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

OmarSubmitted. The report classifies the orphan-order population into the three mutually-exclusive date-comparison categories, with order count, % share, distinct customers, and EUR-converted discount totals per category, plus a running total column to verify the 291 check.

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

Omar's transcript (285 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (5 KB) · the night's reports (2 KB) · the week's memo (3 KB)

Root cause of the 291 'unexplained' active-bucket orphan 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

Omar writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q365

Everything, as recorded