Queries · q364 · Head of Pricing and Promotions · week 2, night 2

Tenant-level breakdown of the lapsed-subscriber leakage trend, by half-year

“Is the decline in lapsed-subscriber free-delivery leakage since late 2024 broad-based across all affected tenants, or driven by one or two tenants fixing something while others stayed flat?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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 2, request 3 of 3
Night 1 raised one real problem and gave two solid answers:

Report 1 (the 'active' bucket breakdown) came back broken: only 1 of the 3 categories printed (88 orders, 30.2%), leaving 203 of the 291 orders unaccounted for entirely rather than showing 0-count rows. That smells like an inner-join dropping orders that had no matching 'older subscription' row — i.e. the 'preceded first-ever subscription' case was silently excluded rather than counted as zero. I can't hand Marco a number built on a query that's missing 70% of its own population, so this is priority one tonight: re-run with explicit NOT EXISTS/LEFT JOIN logic and a self-check total that must sum to 291.

Report 2 is a genuinely good, clean answer to Marco's trend question: leakage rose from ~€200/month in 2023 to a 2024 peak (~€2,000-3,300/month, Feb-Oct 2024), then has declined every month since, down to €819 in Nov 2025 and €0 in Dec 2025. This is the opposite of accelerating — it looks like a historical, now-shrinking gap. I want one more cut — by tenant — before I tell Marco it's broadly resolved, in case the decline is really just one or two tenants fixing something while others stay flat (which would change the "how urgent" answer).

Report 3 (Welcome10 drivers) shows returns spread across many apparel/footwear categories (Outerwear and Boots are the largest single contributors but no runaway concentration), with 9-19 day order-to-return gaps — consistent with normal apparel fit/try-and-return behavior, not quick-flip abuse. Combined with last week's finding that this instance's return rate is only +2.3pp above this tenant's own baseline (which is already ~37%), I think the right framing is shifting: refund-exceeding-discount-cost looks largely mechanical for any % discount at a high-baseline-return apparel merchant, not something the coupon itself is causing. I don't think this needs another report — I'll fold it into the memo with that caveat.

Tonight's three slots: (1) fix the broken categorization query, (2) the size-matched incrementality re-cut Marco asked for, (3) a tenant-level cut of the leakage trend to confirm the decline is broad-based before I call it resolved.
Tenant-level breakdown of the lapsed-subscriber leakage trend, by half-year
Question
Is the decline in lapsed-subscriber free-delivery leakage since late 2024 broad-based across all affected tenants, or driven by one or two tenants fixing something while others stayed flat?
One row per
tenant and half-year period (2023-H1 through 2025-H2, 6 periods)
Columns
- tenant name
- half-year period
- orphan orders where most-recent subscription status = 'canceled' (count)
- distinct customers
- total discount amount (EUR)

Ines 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 tenants_list AS (
  SELECT id AS tenant_id, name AS tenant_name
  FROM tenants
  WHERE name IN ('Grind Collective','Honeycomb Larder','Kibblewick','Feather & Fin','Saffron & Sage','The Balm Room')
),
periods AS (
  SELECT '2023-H1' AS period, DATE '2023-01-01' AS start_date, DATE '2023-06-30' AS end_date UNION ALL
  SELECT '2023-H2', DATE '2023-07-01', DATE '2023-12-31' UNION ALL
  SELECT '2024-H1', DATE '2024-01-01', DATE '2024-06-30' UNION ALL
  SELECT '2024-H2', DATE '2024-07-01', DATE '2024-12-31' UNION ALL
  SELECT '2025-H1', DATE '2025-01-01', DATE '2025-06-30' UNION ALL
  SELECT '2025-H2', DATE '2025-07-01', DATE '2025-12-31'
),
base_grid AS (
  SELECT t.tenant_id, t.tenant_name, p.period
  FROM tenants_list t CROSS JOIN periods p
),
orphan_order_discounts AS (
  SELECT
    o.tenant_id,
    o.id AS order_id,
    o.user_id,
    o.placed_at,
    od.amount AS discount_amount,
    od.currency_code,
    CASE WHEN EXTRACT(MONTH FROM o.placed_at) <= 6
         THEN CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H1'
         ELSE CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H2'
    END AS period
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.tenant_id IN (SELECT tenant_id FROM tenants_list)
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s2
      WHERE s2.tenant_id = o.tenant_id AND s2.user_id = o.user_id
        AND s2.status IN ('active','trialing','past_due')
        AND s2.started_at <= o.placed_at
        AND (s2.ended_at IS NULL OR s2.ended_at >= o.placed_at)
    )
    AND EXISTS (
      SELECT 1 FROM (
        SELECT s3.status, s3.user_id,
               ROW_NUMBER() OVER (PARTITION BY s3.user_id ORDER BY s3.started_at DESC, s3.id DESC) AS rn
        FROM subscriptions s3
        WHERE s3.tenant_id = o.tenant_id AND s3.user_id = o.user_id
      ) latest
      WHERE latest.rn = 1 AND latest.status = 'canceled'
    )
),
agg AS (
  SELECT ood.tenant_id, ood.period,
         COUNT(DISTINCT ood.order_id) AS orphan_orders,
         COUNT(DISTINCT ood.user_id) AS distinct_customers,
         SUM(ood.discount_amount / COALESCE(er.rate,1)) AS total_discount_eur
  FROM orphan_order_discounts ood
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ood.currency_code
   AND er.as_of = CAST(ood.placed_at AS DATE)
  GROUP BY ood.tenant_id, ood.period
)
SELECT
  g.tenant_name,
  g.period AS half_year,
  COALESCE(a.orphan_orders,0) AS orphan_orders,
  COALESCE(a.distinct_customers,0) AS distinct_customers,
  ROUND(COALESCE(a.total_discount_eur,0),2) AS total_discount_eur
FROM base_grid g
LEFT JOIN agg a ON a.tenant_id = g.tenant_id AND a.period = g.period
ORDER BY g.tenant_name, g.period

“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 is a tenant × half-year grid (36 rows) with orphan order count, distinct customers, and EUR discount total, zero-filled for all 6 identified tenants across 2023-H1 through 2025-H2 — ready to show whether the leakage decline is concentrated in a couple of tenants or broad-based.”

The report comes back overnight

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

tenant_name      | half_year | orphan_orders | distinct_customers | total_discount_eur
-----------------+-----------+---------------+--------------------+-------------------
Feather & Fin    | 2023-H1   |             2 |                  2 |             135.68
Feather & Fin    | 2023-H2   |             2 |                  2 |             113.74
Feather & Fin    | 2024-H1   |            24 |                  6 |             412.46
Feather & Fin    | 2024-H2   |            36 |                  7 |            1015.47
Feather & Fin    | 2025-H1   |            35 |                  7 |             959.48
Feather & Fin    | 2025-H2   |             4 |                  2 |              92.02
Grind Collective | 2023-H1   |            33 |                  8 |             720.53
Grind Collective | 2023-H2   |            64 |                 14 |            1991.57
Grind Collective | 2024-H1   |           112 |                 23 |             5914.3
Grind Collective | 2024-H2   |           115 |                 24 |            7393.65
Grind Collective | 2025-H1   |            80 |                 17 |            4994.54
Grind Collective | 2025-H2   |            46 |                 12 |            3219.31
Honeycomb Larder | 2023-H1   |            32 |                  9 |            1751.05
Honeycomb Larder | 2023-H2   |            46 |                 14 |            1652.82
Honeycomb Larder | 2024-H1   |           118 |                 23 |            5282.23
Honeycomb Larder | 2024-H2   |           111 |                 22 |            5778.15
Honeycomb Larder | 2025-H1   |            76 |                 17 |            4020.56
Honeycomb Larder | 2025-H2   |            27 |                  7 |            1025.16
Kibblewick       | 2023-H1   |             0 |                  0 |                0.0
Kibblewick       | 2023-H2   |             0 |                  0 |                0.0
Kibblewick       | 2024-H1   |            26 |                  9 |             792.68
Kibblewick       | 2024-H2   |            51 |                 14 |            1680.51
Kibblewick       | 2025-H1   |            65 |                 16 |            1755.31
Kibblewick       | 2025-H2   |            22 |                  8 |             734.29
Saffron & Sage   | 2023-H1   |             8 |                  3 |             646.41
Saffron & Sage   | 2023-H2   |             6 |                  1 |             580.86
Saffron & Sage   | 2024-H1   |             8 |                  2 |             556.78
Saffron & Sage   | 2024-H2   |             7 |                  3 |             517.12
Saffron & Sage   | 2025-H1   |            12 |                  5 |            1315.03
Saffron & Sage   | 2025-H2   |             5 |                  2 |             659.93
The Balm Room    | 2023-H1   |             1 |                  1 |             118.01
The Balm Room    | 2023-H2   |             6 |                  1 |             696.45
The Balm Room    | 2024-H1   |             6 |                  1 |             862.22
The Balm Room    | 2024-H2   |             1 |                  1 |               91.5
The Balm Room    | 2025-H1   |             1 |                  1 |             178.84
The Balm Room    | 2025-H2   |             0 |                  0 |                0.0
(36 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:
- q367Reconciled 2025 orphan population by tenant and subscription status (single pass)
It builds on:
- q360Monthly trend of confirmed lapsed-subscriber free-delivery leakage, 2023-2025

It becomes benchmark query q364

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

determinismtie-breaker: tenant names are not unique; (tenant_id, period) is unique in base_grid
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 (12×)
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 tenants_list AS (  SELECT id AS tenant_id, name AS tenant_name  FROM tenants  WHERE name IN ('Grind Collective','Honeycomb Larder','Kibblewick','Feather & Fin','Saffron & Sage','The Balm Room')),periods AS (  SELECT '2023-H1' AS period, DATE '2023-01-01' AS start_date, DATE '2023-06-30' AS end_date UNION ALL  SELECT '2023-H2', DATE '2023-07-01', DATE '2023-12-31' UNION ALL  SELECT '2024-H1', DATE '2024-01-01', DATE '2024-06-30' UNION ALL  SELECT '2024-H2', DATE '2024-07-01', DATE '2024-12-31' UNION ALL  SELECT '2025-H1', DATE '2025-01-01', DATE '2025-06-30' UNION ALL  SELECT '2025-H2', DATE '2025-07-01', DATE '2025-12-31'),base_grid AS (  SELECT t.tenant_id, t.tenant_name, p.period  SELECT t.tenant_id AS tenant_id, t.tenant_name AS tenant_name, p.period AS period  FROM tenants_list t CROSS JOIN periods p),orphan_order_discounts AS (  SELECT    o.tenant_id,    o.tenant_id AS tenant_id,    o.id AS order_id,    o.user_id,    o.placed_at,    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,    CASE WHEN EXTRACT(MONTH FROM o.placed_at) <= 6         THEN CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H1'         ELSE CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H2'    END AS period  FROM orders o  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id  WHERE d.name = 'Subscription delivery (prepaid)'    AND o.placed_at >= DATE '2023-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'    AND o.tenant_id IN (SELECT tenant_id FROM tenants_list)    AND NOT EXISTS (      SELECT 1 FROM subscriptions s2      WHERE s2.tenant_id = o.tenant_id AND s2.user_id = o.user_id        AND s2.status IN ('active','trialing','past_due')        AND s2.started_at <= o.placed_at        AND (s2.ended_at IS NULL OR s2.ended_at >= o.placed_at)    )    AND EXISTS (      SELECT 1 FROM (        SELECT s3.status, s3.user_id,               ROW_NUMBER() OVER (PARTITION BY s3.user_id ORDER BY s3.started_at DESC, s3.id DESC) AS rn        SELECT s3.status AS status, s3.user_id AS user_id,               ROW_NUMBER() OVER (PARTITION BY s3.user_id ORDER BY s3.started_at DESC NULLS FIRST, s3.id DESC NULLS FIRST) AS rn        FROM subscriptions s3        WHERE s3.tenant_id = o.tenant_id AND s3.user_id = o.user_id      ) latest      WHERE latest.rn = 1 AND latest.status = 'canceled'    )),agg AS (  SELECT ood.tenant_id, ood.period,  SELECT ood.tenant_id AS tenant_id, ood.period AS period,         COUNT(DISTINCT ood.order_id) AS orphan_orders,         COUNT(DISTINCT ood.user_id) AS distinct_customers,         SUM(ood.discount_amount / COALESCE(er.rate,1)) AS total_discount_eur         SUM(CAST(ood.discount_amount / COALESCE(er.rate,1) AS NUMERIC(18,6))) AS total_discount_eur  FROM orphan_order_discounts ood  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = ood.currency_code   AND er.as_of = CAST(ood.placed_at AS DATE)  GROUP BY ood.tenant_id, ood.period)SELECT  g.tenant_name,  g.tenant_name AS tenant_name,  g.period AS half_year,  COALESCE(a.orphan_orders,0) AS orphan_orders,  COALESCE(a.distinct_customers,0) AS distinct_customers,  ROUND(COALESCE(a.total_discount_eur,0),2) AS total_discount_eur  COALESCE(a.total_discount_eur,0) AS total_discount_eurFROM base_grid gLEFT JOIN agg a ON a.tenant_id = g.tenant_id AND a.period = g.periodORDER BY g.tenant_name, g.periodORDER BY g.tenant_name NULLS LAST, g.period NULLS LAST, g.tenant_id NULLS LAST

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

WITH tenants_list AS (
  SELECT id AS tenant_id, name AS tenant_name
  FROM tenants
  WHERE name IN ('Grind Collective','Honeycomb Larder','Kibblewick','Feather & Fin','Saffron & Sage','The Balm Room')
),
periods AS (
  SELECT '2023-H1' AS period, DATE '2023-01-01' AS start_date, DATE '2023-06-30' AS end_date UNION ALL
  SELECT '2023-H2', DATE '2023-07-01', DATE '2023-12-31' UNION ALL
  SELECT '2024-H1', DATE '2024-01-01', DATE '2024-06-30' UNION ALL
  SELECT '2024-H2', DATE '2024-07-01', DATE '2024-12-31' UNION ALL
  SELECT '2025-H1', DATE '2025-01-01', DATE '2025-06-30' UNION ALL
  SELECT '2025-H2', DATE '2025-07-01', DATE '2025-12-31'
),
base_grid AS (
  SELECT t.tenant_id AS tenant_id, t.tenant_name AS tenant_name, p.period AS period
  FROM tenants_list t CROSS JOIN periods p
),
orphan_order_discounts AS (
  SELECT
    o.tenant_id AS tenant_id,
    o.id AS order_id,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    od.amount AS discount_amount,
    od.currency_code AS currency_code,
    CASE WHEN EXTRACT(MONTH FROM o.placed_at) <= 6
         THEN CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H1'
         ELSE CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H2'
    END AS period
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.tenant_id IN (SELECT tenant_id FROM tenants_list)
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s2
      WHERE s2.tenant_id = o.tenant_id AND s2.user_id = o.user_id
        AND s2.status IN ('active','trialing','past_due')
        AND s2.started_at <= o.placed_at
        AND (s2.ended_at IS NULL OR s2.ended_at >= o.placed_at)
    )
    AND EXISTS (
      SELECT 1 FROM (
        SELECT s3.status AS status, s3.user_id AS user_id,
               ROW_NUMBER() OVER (PARTITION BY s3.user_id ORDER BY s3.started_at DESC NULLS FIRST, s3.id DESC NULLS FIRST) AS rn
        FROM subscriptions s3
        WHERE s3.tenant_id = o.tenant_id AND s3.user_id = o.user_id
      ) latest
      WHERE latest.rn = 1 AND latest.status = 'canceled'
    )
),
agg AS (
  SELECT ood.tenant_id AS tenant_id, ood.period AS period,
         COUNT(DISTINCT ood.order_id) AS orphan_orders,
         COUNT(DISTINCT ood.user_id) AS distinct_customers,
         SUM(CAST(ood.discount_amount / COALESCE(er.rate,1) AS NUMERIC(18,6))) AS total_discount_eur
  FROM orphan_order_discounts ood
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ood.currency_code
   AND er.as_of = CAST(ood.placed_at AS DATE)
  GROUP BY ood.tenant_id, ood.period
)
SELECT
  g.tenant_name AS tenant_name,
  g.period AS half_year,
  COALESCE(a.orphan_orders,0) AS orphan_orders,
  COALESCE(a.distinct_customers,0) AS distinct_customers,
  COALESCE(a.total_discount_eur,0) AS total_discount_eur
FROM base_grid g
LEFT JOIN agg a ON a.tenant_id = g.tenant_id AND a.period = g.period
ORDER BY g.tenant_name NULLS LAST, g.period NULLS LAST, g.tenant_id NULLS LAST

Firebolt{ "errors": [ { "description": "Line 59, Column 39: 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": "0649f84b-07e2-47a3-892e-885c0a1d4558", "query_lab
DataFusionSchema error: No field named latest.tenant_id. Did you mean 'latest.user_id'?
ClickHouseCode: 48. DB::Exception: Cannot decorrelate query, because 'Window' step is not supported. (NOT_IMPLEMENTED)

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

Ines's whole session, 7 turns

The request, as Ines received it

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

Title: Tenant-level breakdown of the lapsed-subscriber leakage trend, by half-year
Question: Is the decline in lapsed-subscriber free-delivery leakage since late 2024 broad-based across all affected tenants, or driven by one or two tenants fixing something while others stayed flat?
Grain: one row per tenant and half-year period (2023-H1 through 2025-H2, 6 periods)
Columns:
- tenant name
- half-year period
- orphan orders where most-recent subscription status = 'canceled' (count)
- distinct customers
- total discount amount (EUR)
Details: Source: same confirmed-leakage population as the monthly trend report (order_discounts + discounts name='Subscription delivery (prepaid)' + orders placed_at 2023-01-01 to 2025-12-31, status != 'cancelled', no overlapping active/trialing/past_due subscription, most-recent subscription status = 'canceled') joined to tenants for name. Bucket placed_at into half-year periods (2023-H1 = Jan-Jun 2023, 2023-H2 = Jul-Dec 2023, etc. through 2025-H2). Convert discount amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Include all 6 tenants identified in the original subscription-delivery analysis (Grind Collective, Honeycomb Larder, Kibblewick, Feather & Fin, Saffron & Sage, The Balm Room) even in periods with zero orphan orders (zero-fill). Order by tenant name, then period ascending. Print all rows (expect up to 36).

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

Ines calls dry_run

WITH tenants_list AS (
  SELECT id AS tenant_id, name AS tenant_name
  FROM tenants
  WHERE name IN ('Grind Collective','Honeycomb Larder','Kibblewick','Feather & Fin','Saffron & Sage','The Balm Room')
),
periods AS (
  SELECT '2023-H1' AS period, DATE '2023-01-01' AS start_date, DATE '2023-06-30' AS end_date UNION ALL
  SELECT '2023-H2', DATE '2023-07-01', DATE '2023-12-31' UNION ALL
  SELECT '2024-H1', DATE '2024-01-01', DATE '2024-06-30' UNION ALL
  SELECT '2024-H2', DATE '2024-07-01', DATE '2024-12-31' UNION ALL
  SELECT '2025-H1', DATE '2025-01-01', DATE '2025-06-30' UNION ALL
  SELECT '2025-H2', DATE '2025-07-01', DATE '2025-12-31'
),
base_grid AS (
  SELECT t.tenant_id, t.tenant_name, p.period
  FROM tenants_list t CROSS JOIN periods p
),
orphan_order_discounts AS (
  SELECT
    o.tenant_id,
    o.id AS order_id,
    o.user_id,
    o.placed_at,
    od.amount AS discount_amount,
    od.currency_code,
    CASE WHEN EXTRACT(MONTH FROM o.placed_at) <= 6
         THEN CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H1'
         ELSE CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H2'
    END AS period
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.tenant_id IN (SELECT tenant_id FROM tenants_list)
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s2
      WHERE s2.tenant_id = o.tenant_id AND s2.user_id = o.user_id
        AND s2.status IN ('active','trialing','past_due')
        AND s2.started_at <= o.placed_at
        AND (s2.ended_at IS NULL OR s2.ended_at >= o.placed_at)
    )
    AND EXISTS (
      SELECT 1 FROM (
        SELECT s3.status, s3.user_id,
               ROW_NUMBER() OVER (PARTITION BY s3.user_id ORDER BY s3.started_at DESC, s3.id DESC) AS rn
        FROM subscriptions s3
        WHERE s3.tenant_id = o.tenant_id AND s3.user_id = o.user_id
      ) latest
      WHERE latest.rn = 1 AND latest.status = 'canceled'
    )
),
agg AS (
  SELECT ood.tenant_id, ood.period,
         COUNT(DISTINCT ood.order_id) AS orphan_orders,
         COUNT(DISTINCT ood.user_id) AS distinct_customers,
         SUM(ood.discount_amount / COALESCE(er.rate,1)) AS total_discount_eur
  FROM orphan_order_discounts ood
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ood.currency_code
   AND er.as_of = CAST(ood.placed_at AS DATE)
  GROUP BY ood.tenant_id, ood.period
)
SELECT
  g.tenant_name,
  g.period AS half_year,
  COALESCE(a.orphan_orders,0) AS orphan_orders,
  COALESCE(a.distinct_customers,0) AS distinct_customers,
  ROUND(COALESCE(a.total_discount_eur,0),2) AS total_discount_eur
FROM base_grid g
LEFT JOIN agg a ON a.tenant_id = g.tenant_id AND a.period = g.period
ORDER BY g.tenant_name, g.period

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenant_name, half_year, orphan_orders, distinct_customers, total_discount_eur

InesQuery compiles and returns the expected columns. Submitting.

Ines calls submit

WITH tenants_list AS (
  SELECT id AS tenant_id, name AS tenant_name
  FROM tenants
  WHERE name IN ('Grind Collective','Honeycomb Larder','Kibblewick','Feather & Fin','Saffron & Sage','The Balm Room')
),
periods AS (
  SELECT '2023-H1' AS period, DATE '2023-01-01' AS start_date, DATE '2023-06-30' AS end_date UNION ALL
  SELECT '2023-H2', DATE '2023-07-01', DATE '2023-12-31' UNION ALL
  SELECT '2024-H1', DATE '2024-01-01', DATE '2024-06-30' UNION ALL
  SELECT '2024-H2', DATE '2024-07-01', DATE '2024-12-31' UNION ALL
  SELECT '2025-H1', DATE '2025-01-01', DATE '2025-06-30' UNION ALL
  SELECT '2025-H2', DATE '2025-07-01', DATE '2025-12-31'
),
base_grid AS (
  SELECT t.tenant_id, t.tenant_name, p.period
  FROM tenants_list t CROSS JOIN periods p
),
orphan_order_discounts AS (
  SELECT
    o.tenant_id,
    o.id AS order_id,
    o.user_id,
    o.placed_at,
    od.amount AS discount_amount,
    od.currency_code,
    CASE WHEN EXTRACT(MONTH FROM o.placed_at) <= 6
         THEN CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H1'
         ELSE CAST(EXTRACT(YEAR FROM o.placed_at) AS VARCHAR) || '-H2'
    END AS period
  FROM orders o
  JOIN order_discounts od ON od.order_id = o.id AND od.tenant_id = o.tenant_id
  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = o.tenant_id
  WHERE d.name = 'Subscription delivery (prepaid)'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
    AND o.tenant_id IN (SELECT tenant_id FROM tenants_list)
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions s2
      WHERE s2.tenant_id = o.tenant_id AND s2.user_id = o.user_id
        AND s2.status IN ('active','trialing','past_due')
        AND s2.started_at <= o.placed_at
        AND (s2.ended_at IS NULL OR s2.ended_at >= o.placed_at)
    )
    AND EXISTS (
      SELECT 1 FROM (
        SELECT s3.status, s3.user_id,
               ROW_NUMBER() OVER (PARTITION BY s3.user_id ORDER BY s3.started_at DESC, s3.id DESC) AS rn
        FROM subscriptions s3
        WHERE s3.tenant_id = o.tenant_id AND s3.user_id = o.user_id
      ) latest
      WHERE latest.rn = 1 AND latest.status = 'canceled'
    )
),
agg AS (
  SELECT ood.tenant_id, ood.period,
         COUNT(DISTINCT ood.order_id) AS orphan_orders,
         COUNT(DISTINCT ood.user_id) AS distinct_customers,
         SUM(ood.discount_amount / COALESCE(er.rate,1)) AS total_discount_eur
  FROM orphan_order_discounts ood
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
   AND er.quote_code = ood.currency_code
   AND er.as_of = CAST(ood.placed_at AS DATE)
  GROUP BY ood.tenant_id, ood.period
)
SELECT
  g.tenant_name,
  g.period AS half_year,
  COALESCE(a.orphan_orders,0) AS orphan_orders,
  COALESCE(a.distinct_customers,0) AS distinct_customers,
  ROUND(COALESCE(a.total_discount_eur,0),2) AS total_discount_eur
FROM base_grid g
LEFT JOIN agg a ON a.tenant_id = g.tenant_id AND a.period = g.period
ORDER BY g.tenant_name, g.period

The warehouse answers

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

InesSubmitted. The report is a tenant × half-year grid (36 rows) with orphan order count, distinct customers, and EUR discount total, zero-filled for all 6 identified tenants across 2023-H1 through 2025-H2 — ready to show whether the leakage decline is concentrated in a couple of tenants or broad-based.

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

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

Tenant-level breakdown of the lapsed-subscriber leakage trend, by half-year

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

Marco Bianchi writes to Oliver Grant

Oliver Grant plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q364

Everything, as recorded