Queries · q366 · Head of Pricing and Promotions · week 2, night 3
“Is a subscription being marked trialing/active/past_due while already having an ended_at in the past a one-off to the subscription-delivery analysis, or a broader data-quality pattern across the platform?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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?
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:
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
The analyst · week 2, night 3, request 2 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.
The engineer · 1 dry run on the empty schema, then the submission
select status, count(*) as total_subscriptions, count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') as past_ended_count, round(100.0 * count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') / nullif(count(*), 0), 2) as pct_past_ended, count(*) filter (where ended_at is null or ended_at >= timestamp '2026-01-05') as status_consistent_count from subscriptions where status in ('trialing', 'active', 'past_due') group by status order by pct_past_ended desc, status
“Query compiles cleanly. Submitting.”
“Submitted. The report has one row per status (trialing, active, past_due) with total subscriptions, count with a past ended_at, the percentage, and the status-consistent count, ordered by % past-ended descending.”
As Oliver Grant saw it, from the small dataset the agents worked on
status | total_subscriptions | past_ended_count | pct_past_ended | status_consistent_count ---------+---------------------+------------------+----------------+------------------------ active | 411 | 0 | 0.0 | 411 past_due | 1 | 0 | 0.0 | 1 (2 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
select status, count(*) as total_subscriptions, count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') as past_ended_count, round(100.0 * count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') / nullif(count(*), 0), 2) as pct_past_ended, 100.0 * count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') / nullif(count(*), 0) as pct_past_ended, count(*) filter (where ended_at is null or ended_at >= timestamp '2026-01-05') as status_consistent_countfrom subscriptionswhere status in ('trialing', 'active', 'past_due')group by statusorder by pct_past_ended desc, statusorder by pct_past_ended desc NULLS FIRST, status NULLS LAST
select status, count(*) as total_subscriptions, count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') as past_ended_count, 100.0 * count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') / nullif(count(*), 0) as pct_past_ended, count(*) filter (where ended_at is null or ended_at >= timestamp '2026-01-05') as status_consistent_count from subscriptions where status in ('trialing', 'active', 'past_due') group by status order by pct_past_ended desc NULLS FIRST, status NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):
Title: Platform-wide check: how often do 'currently active' subscriptions already have a past end date?
Question: Is a subscription being marked trialing/active/past_due while already having an ended_at in the past a one-off to the subscription-delivery analysis, or a broader data-quality pattern across the platform?
Grain: one row per subscription status (trialing, active, past_due)
Columns:
- status
- total subscriptions with this status (platform-wide, all tenants, current snapshot)
- count with ended_at not null and ended_at before 2026-01-05 (today)
- % with a past ended_at despite this status
- count with ended_at null or in the future (status-consistent)
Details: Source: subscriptions table, all tenants, no date filter on started_at (current full snapshot as of today, 2026-01-05). Filter to status in ('trialing','active','past_due'). For each status, count total rows, count rows where ended_at is not null and ended_at < 2026-01-05, and compute the percentage. Order by % with a past ended_at descending. Print all 3 rows.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runselect status, count(*) as total_subscriptions, count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') as past_ended_count, round(100.0 * count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') / nullif(count(*), 0), 2) as pct_past_ended, count(*) filter (where ended_at is null or ended_at >= timestamp '2026-01-05') as status_consistent_count from subscriptions where status in ('trialing', 'active', 'past_due') group by status order by pct_past_ended desc, status
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: status, total_subscriptions, past_ended_count, pct_past_ended, status_consistent_count
submitselect status, count(*) as total_subscriptions, count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') as past_ended_count, round(100.0 * count(*) filter (where ended_at is not null and ended_at < timestamp '2026-01-05') / nullif(count(*), 0), 2) as pct_past_ended, count(*) filter (where ended_at is null or ended_at >= timestamp '2026-01-05') as status_consistent_count from subscriptions where status in ('trialing', 'active', 'past_due') group by status order by pct_past_ended desc, status
Submitted to tonight's batch. Your work on this request is done.