DB Olympics

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

Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025

“Is the confirmed leakage (free deliveries going to customers whose subscription had already been canceled) a slow-burning historical gap, steady, or accelerating?”

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 SQLViktorOld-school warehouse engineer: explicit JOIN chains straight to the base tables, derived tables, GROUP BY with CASE pivots, few CTEs.

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

  3. Oliver Grant plans the night

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

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

    Priority tonight is the subscription-leakage story since Marco explicitly wants it checked before taking it upstairs, so two of three slots go there:

    1. A methodology check on the 18%/€11.7k "active" bucket from last week — I suspect this isn't a real gap but an artifact of how I categorized customers by their single most-recent subscription record: if their current subscription started after the free order, there may well have been an older (now-superseded) subscription that actually covered the order date, which would mean it belongs with the "canceled/lapsed" leakage story rather than being unexplained. I want this resolved before stating a number to Tobias.
    2. A monthly trend (full 2023-2025 history) on the confirmed "canceled-subscriber" leakage bucket, to tell Marco whether this is a slow-burning historical gap or getting worse — directly answers his accelerating-or-steady question.

    Third slot goes to digging one layer into Welcome10's refund pattern (which category of products it clusters in, and whether return timing looks like fit/expectation mismatch vs. quick-flip behavior) — the one piece Marco called out as worth a deeper look before any capping conversation. I'm holding the harder, size-matched incrementality re-cut for night 2, since it needs more careful design and isn't needed before Wednesday's sync, whereas the subscription-leakage number is.

    Also noting for context (not acting on tonight): Growth Marketing's company-wide finding that discounted orders return 5-7pts more with no basket-size boost in apparel/electronics lines up with our Welcome10 finding — useful external corroboration, not a new data pull.

    Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025
    Question
    Is the confirmed leakage (free deliveries going to customers whose subscription had already been canceled) a slow-burning historical gap, steady, or accelerating?
    One row per
    calendar month, 2023-01 through 2025-12
    Columns
    • month (YYYY-MM)
    • orphan orders where most-recent subscription status = 'canceled' (count)
    • distinct customers
    • total discount amount (EUR)
    • cumulative total discount amount, running sum since 2023-01 (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 months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
latest_sub AS (
  SELECT tenant_id, user_id, status,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY started_at DESC, id DESC) AS rn
  FROM subscriptions
),
confirmed_canceled_users AS (
  SELECT tenant_id, user_id
  FROM latest_sub
  WHERE rn = 1 AND status = 'canceled'
),
orphan_orders AS (
  SELECT
    o.tenant_id,
    o.user_id,
    o.id AS order_id,
    cd.month_label,
    od.amount / er.rate AS discount_amount_eur
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
  JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS DATE)
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = od.currency_code AND er.as_of = CAST(o.placed_at AS DATE)
  JOIN confirmed_canceled_users ccu ON ccu.tenant_id = o.tenant_id AND ccu.user_id = o.user_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.status != 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    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)
    )
),
monthly AS (
  SELECT m.month_label, m.month_start,
         COUNT(oo.order_id) AS orphan_order_count,
         COUNT(DISTINCT oo.user_id) AS distinct_customers,
         COALESCE(SUM(oo.discount_amount_eur),0) AS total_discount_eur
  FROM months m
  LEFT JOIN orphan_orders oo ON oo.month_label = m.month_label
  GROUP BY m.month_label, m.month_start
)
SELECT
  month_label AS month,
  orphan_order_count AS orphan_orders,
  distinct_customers,
  ROUND(total_discount_eur, 2) AS total_discount_eur,
  ROUND(SUM(total_discount_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_discount_eur
FROM monthly
ORDER BY month_start

    “Query compiles and returns exactly the requested grain and 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 the approach: - Orphan definition: order carries a Subscription delivery (prepaid) discount, order isn't cancelled, and the customer has no subscription (trialing/active/past_due) whose started_at/ended_at window covers the order's placed_at. - Confirmed-leakage filter: additionally restricted to customers whose most-recent subscription (by started_at) currently has status canceled. - Currency: order_discounts.amount converted to EUR via exchange_rates (base EUR, quote = order currency, as_of = order date). - Grain: one row per month, all 36 months 2023-01–2025-12 printed (zero-filled via calendar_days where no leakage occurred), with a running cumulative EUR total.”

  5. The report comes back overnight

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

    month   | orphan_orders | distinct_customers | total_discount_eur | cumulative_discount_eur
--------+---------------+--------------------+--------------------+------------------------
2023-01 |             7 |                  6 |             209.68 |                  209.68
2023-02 |            10 |                  9 |             341.12 |                   550.8
2023-03 |            11 |                 10 |              550.0 |                  1100.8
2023-04 |            12 |                 11 |             364.87 |                 1465.67
2023-05 |            19 |                 18 |            1124.67 |                 2590.34
2023-06 |            17 |                 16 |             781.34 |                 3371.68
2023-07 |            19 |                 16 |             743.21 |                 4114.89
2023-08 |            14 |                 14 |              629.7 |                 4744.58
2023-09 |            12 |                 11 |             574.55 |                 5319.14
2023-10 |            22 |                 19 |             773.46 |                  6092.6
2023-11 |            25 |                 21 |             932.82 |                 7025.42
2023-12 |            32 |                 25 |             1381.7 |                 8407.12
2024-01 |            34 |                 29 |            1775.32 |                10182.44
2024-02 |            42 |                 36 |            2374.91 |                12557.35
2024-03 |            47 |                 39 |            2423.11 |                14980.46
2024-04 |            55 |                 49 |            1921.69 |                16902.15
2024-05 |            54 |                 48 |            2383.66 |                19285.81
2024-06 |            62 |                 51 |            2941.98 |                22227.79
2024-07 |            59 |                 51 |            2730.51 |                 24958.3
2024-08 |            53 |                 49 |            2749.51 |                27707.81
2024-09 |            56 |                 50 |            3274.61 |                30982.42
2024-10 |            59 |                 51 |            3310.78 |                 34293.2
2024-11 |            47 |                 42 |            2102.55 |                36395.75
2024-12 |            47 |                 41 |            2308.44 |                38704.19
2025-01 |            46 |                 43 |            3105.47 |                41809.66
2025-02 |            53 |                 49 |            2591.84 |                 44401.5
2025-03 |            48 |                 43 |            2069.43 |                46470.94
2025-04 |            45 |                 38 |             1950.8 |                48421.74
2025-05 |            42 |                 35 |            1693.14 |                50114.88
2025-06 |            35 |                 29 |            1813.07 |                51927.95
2025-07 |            27 |                 22 |            1407.72 |                53335.67
2025-08 |            23 |                 20 |            1293.53 |                 54629.2
2025-09 |            23 |                 21 |            1215.58 |                55844.79
2025-10 |            17 |                 13 |              994.8 |                56839.59
2025-11 |            14 |                 13 |             819.07 |                57658.66
2025-12 |             0 |                  0 |                0.0 |                57658.66
(36 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:

    It builds on:

  7. It becomes benchmark query q360

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

    • determinismtie-breaker in the running sum's window order
    • determinismtie-breaker in the top-level order
    • 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 (5×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (6×)
    The benchmark text, against the submitted SQL
    WITH months AS (  SELECT DISTINCT month_label, month_start  FROM calendar_days  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'),latest_sub AS (  SELECT tenant_id, user_id, status,         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY started_at DESC, id DESC) AS rn         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY started_at DESC NULLS FIRST, id DESC NULLS FIRST) AS rn  FROM subscriptions),confirmed_canceled_users AS (  SELECT tenant_id, user_id  FROM latest_sub  WHERE rn = 1 AND status = 'canceled'),orphan_orders AS (  SELECT    o.tenant_id,    o.user_id,    o.tenant_id AS tenant_id,    o.user_id AS user_id,    o.id AS order_id,    cd.month_label,    od.amount / er.rate AS discount_amount_eur    cd.month_label AS month_label,    CAST(od.amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur  FROM order_discounts od  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id  JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id  JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS DATE)  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = od.currency_code AND er.as_of = CAST(o.placed_at AS DATE)  JOIN confirmed_canceled_users ccu ON ccu.tenant_id = o.tenant_id AND ccu.user_id = o.user_id  WHERE d.name = 'Subscription delivery (prepaid)'    AND o.status != 'cancelled'    AND o.placed_at >= TIMESTAMP '2023-01-01'    AND o.placed_at < TIMESTAMP '2026-01-01'    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)    )),monthly AS (  SELECT m.month_label, m.month_start,  SELECT m.month_label AS month_label, m.month_start AS month_start,         COUNT(oo.order_id) AS orphan_order_count,         COUNT(DISTINCT oo.user_id) AS distinct_customers,         COALESCE(SUM(oo.discount_amount_eur),0) AS total_discount_eur  FROM months m  LEFT JOIN orphan_orders oo ON oo.month_label = m.month_label  GROUP BY m.month_label, m.month_start)SELECT  month_label AS month,  orphan_order_count AS orphan_orders,  distinct_customers,  ROUND(total_discount_eur, 2) AS total_discount_eur,  ROUND(SUM(total_discount_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_discount_eur  total_discount_eur AS total_discount_eur,  SUM(total_discount_eur) OVER (ORDER BY month_start NULLS LAST, month_label NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_discount_eurFROM monthlyORDER BY month_startORDER BY month_start NULLS LAST, month_label NULLS LAST

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

    WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
latest_sub AS (
  SELECT tenant_id, user_id, status,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY started_at DESC NULLS FIRST, id DESC NULLS FIRST) AS rn
  FROM subscriptions
),
confirmed_canceled_users AS (
  SELECT tenant_id, user_id
  FROM latest_sub
  WHERE rn = 1 AND status = 'canceled'
),
orphan_orders AS (
  SELECT
    o.tenant_id AS tenant_id,
    o.user_id AS user_id,
    o.id AS order_id,
    cd.month_label AS month_label,
    CAST(od.amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
  JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS DATE)
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = od.currency_code AND er.as_of = CAST(o.placed_at AS DATE)
  JOIN confirmed_canceled_users ccu ON ccu.tenant_id = o.tenant_id AND ccu.user_id = o.user_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.status != 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    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)
    )
),
monthly AS (
  SELECT m.month_label AS month_label, m.month_start AS month_start,
         COUNT(oo.order_id) AS orphan_order_count,
         COUNT(DISTINCT oo.user_id) AS distinct_customers,
         COALESCE(SUM(oo.discount_amount_eur),0) AS total_discount_eur
  FROM months m
  LEFT JOIN orphan_orders oo ON oo.month_label = m.month_label
  GROUP BY m.month_label, m.month_start
)
SELECT
  month_label AS month,
  orphan_order_count AS orphan_orders,
  distinct_customers,
  total_discount_eur AS total_discount_eur,
  SUM(total_discount_eur) OVER (ORDER BY month_start NULLS LAST, month_label NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_discount_eur
FROM monthly
ORDER BY month_start NULLS LAST, month_label NULLS LAST
    100 ms1 s10 s30 sHyper2.52 sDuckDBtimeout (30 s)GizmoSQLtimeout (30 s)clickhouse_brew2.45 sFirebolterrorDataFusion796 msClickHouseerror
    • DuckDBno result within 30 s
    • GizmoSQLno result within 30 s
    • Firebolt{ "errors": [ { "description": "Line 22, Column 20: 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": "6b366025-ea50-4021-9b43-20ab619e4c22", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table5.amount Decimal(14, 4) Decimal64(size = 0), __table9.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

    Scale factor 1000, c8a.24xlarge, after the last load. q360 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: Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025
Question: Is the confirmed leakage (free deliveries going to customers whose subscription had already been canceled) a slow-burning historical gap, steady, or accelerating?
Grain: one row per calendar month, 2023-01 through 2025-12
Columns:
- month (YYYY-MM)
- orphan orders where most-recent subscription status = 'canceled' (count)
- distinct customers
- total discount amount (EUR)
- cumulative total discount amount, running sum since 2023-01 (EUR)
Details: Source: order_discounts + discounts (name='Subscription delivery (prepaid)') + orders (placed_at across full history 2023-01-01 to 2025-12-31, status != 'cancelled', user_id, tenant_id) restricted to orders with no overlapping subscription (status in trialing/active/past_due, same overlap definition as before) AND whose customer's most-recent subscription (by started_at) has status = 'canceled' (the confirmed-leakage group, ~82% of orphans, ~€57k in 2025 alone). Join to calendar_days (or extract month from placed_at) to get month_label. Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Order by month ascending. Print all 36 months.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Viktor calls dry_run
      WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
latest_sub AS (
  SELECT tenant_id, user_id, status,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY started_at DESC, id DESC) AS rn
  FROM subscriptions
),
confirmed_canceled_users AS (
  SELECT tenant_id, user_id
  FROM latest_sub
  WHERE rn = 1 AND status = 'canceled'
),
orphan_orders AS (
  SELECT
    o.tenant_id,
    o.user_id,
    o.id AS order_id,
    cd.month_label,
    od.amount / er.rate AS discount_amount_eur
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
  JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS DATE)
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = od.currency_code AND er.as_of = CAST(o.placed_at AS DATE)
  JOIN confirmed_canceled_users ccu ON ccu.tenant_id = o.tenant_id AND ccu.user_id = o.user_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.status != 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    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)
    )
),
monthly AS (
  SELECT m.month_label, m.month_start,
         COUNT(oo.order_id) AS orphan_order_count,
         COUNT(DISTINCT oo.user_id) AS distinct_customers,
         COALESCE(SUM(oo.discount_amount_eur),0) AS total_discount_eur
  FROM months m
  LEFT JOIN orphan_orders oo ON oo.month_label = m.month_label
  GROUP BY m.month_label, m.month_start
)
SELECT
  month_label AS month,
  orphan_order_count AS orphan_orders,
  distinct_customers,
  ROUND(total_discount_eur, 2) AS total_discount_eur,
  ROUND(SUM(total_discount_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_discount_eur
FROM monthly
ORDER BY month_start
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orphan_orders, distinct_customers, total_discount_eur, cumulative_discount_eur
    • ViktorQuery compiles and returns exactly the requested grain and columns. Submitting.
    • Viktor calls submit
      WITH months AS (
  SELECT DISTINCT month_label, month_start
  FROM calendar_days
  WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'
),
latest_sub AS (
  SELECT tenant_id, user_id, status,
         ROW_NUMBER() OVER (PARTITION BY tenant_id, user_id ORDER BY started_at DESC, id DESC) AS rn
  FROM subscriptions
),
confirmed_canceled_users AS (
  SELECT tenant_id, user_id
  FROM latest_sub
  WHERE rn = 1 AND status = 'canceled'
),
orphan_orders AS (
  SELECT
    o.tenant_id,
    o.user_id,
    o.id AS order_id,
    cd.month_label,
    od.amount / er.rate AS discount_amount_eur
  FROM order_discounts od
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
  JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
  JOIN calendar_days cd ON cd.calendar_date = CAST(o.placed_at AS DATE)
  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = od.currency_code AND er.as_of = CAST(o.placed_at AS DATE)
  JOIN confirmed_canceled_users ccu ON ccu.tenant_id = o.tenant_id AND ccu.user_id = o.user_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.status != 'cancelled'
    AND o.placed_at >= TIMESTAMP '2023-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
    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)
    )
),
monthly AS (
  SELECT m.month_label, m.month_start,
         COUNT(oo.order_id) AS orphan_order_count,
         COUNT(DISTINCT oo.user_id) AS distinct_customers,
         COALESCE(SUM(oo.discount_amount_eur),0) AS total_discount_eur
  FROM months m
  LEFT JOIN orphan_orders oo ON oo.month_label = m.month_label
  GROUP BY m.month_label, m.month_start
)
SELECT
  month_label AS month,
  orphan_order_count AS orphan_orders,
  distinct_customers,
  ROUND(total_discount_eur, 2) AS total_discount_eur,
  ROUND(SUM(total_discount_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_discount_eur
FROM monthly
ORDER BY month_start
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • ViktorSubmitted. Summary of the approach: - **Orphan definition**: order carries a `Subscription delivery (prepaid)` discount, order isn't cancelled, and the customer has no subscription (trialing/active/past_due) whose `started_at`/`ended_at` window covers the order's `placed_at`. - **Confirmed-leakage filter**: additionally restricted to customers whose most-recent subscription (by `started_at`) currently has status `canceled`. - **Currency**: `order_discounts.amount` converted to EUR via `exchange_rates` (base `EUR`, quote = order currency, `as_of` = order date). - **Grain**: one row per month, all 36 months 2023-01–2025-12 printed (zero-filled via `calendar_days` where no leakage occurred), with a running cumulative EUR total.

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