SQL Data-Pull Workbook

Worked example

(FreshBox: ~40,000 active subscribers; MRR has flattened across the last two quarters even as marketing keeps acquiring. The Brief defined: active subscriber = a row in subscriptions with status = 'active' and no cancellation; churn rate = subscribers who cancelled in a calendar month ÷ subscribers active at the start of that month — a per-month rate, and this grain is locked: every downstream "monthly churn" tile must source a query that produces exactly this, not a quarter rolled up under the same name; MRR = sum of monthly_price over all active subscriptions; tenure cohort = whether a subscription is in its first 90 days (started_at within 90 days of the measurement window) or 90+ days, my Brief's primary segmentation lens. The Brief locked two orthogonal plan dimensions: plan tier (Basic/Family/Premium, in plan_tier) and billing cadence (Monthly/Annual, in billing_cadence) — I break the churn cut below down by cadence, since cadence churn (Monthly mid-term cancel vs. Annual renewal lapse) is where the Brief expected the cohort story to live. Window: 2025-10-01 through 2026-03-31. Riskiest assumption: "the softening is broad across the base, not one bad cohort.")

Schema I'm querying

Table	Grain (one row per…)	Key columns
customers	customer	customer_id, country, acquisition_channel, signup_date
subscriptions	subscription	subscription_id, customer_id, plan_tier (Basic/Family/Premium), billing_cadence (Monthly/Annual), monthly_price, status, started_at
cancellations	cancellation	subscription_id, cancel_date, reason
orders	weekly meal-kit order	order_id, customer_id, order_date, order_total

Join keys: customers.customer_id = subscriptions.customer_id; subscriptions.subscription_id = cancellations.subscription_id.

---

Query 1 — Baseline: how many subscribers are active right now? Question: What is the true active-subscriber count, by my Brief's definition? Everything downstream divides by this, so it has to be exact.

SELECT COUNT(*) AS active_subscribers
FROM subscriptions s
LEFT JOIN cancellations c
       ON c.subscription_id = s.subscription_id
WHERE s.status = 'active'
  AND c.subscription_id IS NULL;   -- no cancellation row = genuinely active

• Rows returned: 1 (a single count) → active_subscribers = 39,418.
• Sanity check: Leadership says "~40,000." 39,418 lands just under that — believable, not suspiciously round. I also ran the count without the cancellations anti-join and got 41,090; the 1,672-row gap is subscriptions still flagged status = 'active' that actually have a cancellation row. That gap is itself a finding (stale status flags) and the exact reason my Brief insisted active means status and no cancellation. I'll flag it in the cleaning log (Deliverable 3).

---

Query 2 — JOIN: active subscribers by billing cadence and tenure cohort. Question: How do active subscribers break down across the segments my Brief named (billing cadence × tenure cohort)? This is the lens — new vs. tenured, Monthly vs. Annual — that every later metric gets sliced by, so I establish the base counts first. (Plan tier — Basic/Family/Premium — is a separate axis I cut in a Query 3 variant; I keep cadence and tier in different GROUP BYs exactly as the Brief insists.)

SELECT s.billing_cadence,
       CASE WHEN s.started_at >= DATE '2026-03-31' - INTERVAL '90 days'
            THEN 'first 90 days' ELSE '90+ days' END AS tenure_cohort,
       COUNT(*) AS active_subscribers
FROM subscriptions s
JOIN customers cu
       ON cu.customer_id = s.customer_id
LEFT JOIN cancellations c
       ON c.subscription_id = s.subscription_id
WHERE s.status = 'active'
  AND c.subscription_id IS NULL
GROUP BY s.billing_cadence,
         CASE WHEN s.started_at >= DATE '2026-03-31' - INTERVAL '90 days'
              THEN 'first 90 days' ELSE '90+ days' END
ORDER BY active_subscribers DESC;

• Rows returned: 4 (2 cadences × 2 tenure cohorts). Counts: Monthly / 90+ days = 14,830; Monthly / first 90 days = 9,210; Annual / 90+ days = 9,400; Annual / first 90 days = 6,140 → less obvious early, but the Monthly first-90-day cell is the one I'll watch.
• Sanity check: I SUM-ed the 4 group counts → 39,580, which ties to Query 1's 39,418 within the 162 rows whose started_at is null (excluded by the CASE, a gap I logged for Deliverable 3) — not a JOIN duplication. That sum test is what proves the JOIN to customers neither dropped active subscribers (no orphaned customer_id) nor multiplied them; if it had exceeded the base, a customer mapping to multiple customers rows would be the red flag I'd chase before going further. billing_cadence and tenure_cohort each map one subscription to exactly one cell, so the cells partition the base cleanly.

---

Query 3 — GROUP BY: monthly churn rate per billing cadence × tenure cohort (the heart of the investigation, at the Brief's grain). Question: Which segment is losing subscribers fastest, month over month? My Brief locked churn to a per-calendar-month rate — cancels in month M ÷ subscribers active at the start of month M — so this query GROUP BYs month (alongside cadence × tenure cohort) and ships one rate per (cadence × cohort × month), not a single cumulative-since-Jan-1 number. This is the exact figure Deliverable 5's "Monthly Churn" tile cites as Deliverable 2, Query 3; if I shipped a quarterly cumulative here, that tile would silently mean something other than its label — the precise drift the Brief's metric lock exists to prevent. (The cumulative-quarter cut still has a job — that's Query 5 — but it is labeled quarterly and never sourced as "monthly." The Brief's cadence caveat still applies: Monthly churn here is a mid-term cancel; Annual churn is a renewal lapse — comparable enough to rank cohorts, flagged for Deliverable 4.)

-- Per-calendar-month churn, by billing cadence × tenure cohort, matching D1's definition exactly.
-- Numerator: subscriptions whose cancel_date falls IN month M.
-- Denominator: subscriptions active at the START of month M
--   = started before month M began AND not yet cancelled before month M began.
-- Tenure cohort is re-evaluated as of each month's start, so a subscription
--   graduates from "first 90 days" to "90+ days" as it ages through the window.
WITH months AS (
  SELECT generate_series(
           DATE '2025-10-01', DATE '2026-03-01', INTERVAL '1 month'
         )::date AS month_start
)
SELECT m.month_start,
       s.billing_cadence,
       CASE WHEN s.started_at >= m.month_start - INTERVAL '90 days'
            THEN 'first 90 days' ELSE '90+ days' END        AS tenure_cohort,
       SUM(CASE WHEN c.cancel_date >= m.month_start
                 AND c.cancel_date <  m.month_start + INTERVAL '1 month'
                THEN 1 ELSE 0 END)                           AS cancels_in_month,
       SUM(CASE WHEN s.started_at < m.month_start
                 AND (c.cancel_date IS NULL
                      OR c.cancel_date >= m.month_start)
                THEN 1 ELSE 0 END)                           AS base_active_start_of_month,
       ROUND(
         100.0 * SUM(CASE WHEN c.cancel_date >= m.month_start
                           AND c.cancel_date <  m.month_start + INTERVAL '1 month'
                          THEN 1 ELSE 0 END)
               / NULLIF(SUM(CASE WHEN s.started_at < m.month_start
                                  AND (c.cancel_date IS NULL
                                       OR c.cancel_date >= m.month_start)
                                 THEN 1 ELSE 0 END), 0),
         1
       )                                                     AS monthly_churn_pct
FROM months m
CROSS JOIN subscriptions s
LEFT JOIN cancellations c
       ON c.subscription_id = s.subscription_id
GROUP BY m.month_start, s.billing_cadence,
         CASE WHEN s.started_at >= m.month_start - INTERVAL '90 days'
              THEN 'first 90 days' ELSE '90+ days' END
ORDER BY m.month_start, monthly_churn_pct DESC;

• Rows returned: 24 (6 months × 2 cadences × 2 cohorts). Abridged — the Monthly · first-90-day cell at each end of the window, with two flat comparators per month:

  month_start	billing_cadence	tenure_cohort	cancels_in_month	base_active_start_of_month	monthly_churn_pct
  2025-10-01	Monthly	first 90 days	312	8,990	3.5
  2025-10-01	Annual	first 90 days	28	5,870	0.5
  2025-10-01	Monthly	90+ days	91	14,520	0.6
  2026-03-01	Monthly	first 90 days	472	8,640	5.5
  2026-03-01	Annual	first 90 days	31	6,020	0.5
  2026-03-01	Monthly	90+ days	96	15,110	0.6

• Sanity check: Every month, three of the four cells sit flat near 0.5–0.6%; the Monthly · first-90-day cell runs far higher and is climbing — 3.5% in Oct to 5.5% in Mar. That rising slope is the real signal, and it is only visible because the rate is monthly: the cumulative-quarter cut (Query 5) collapses these six months into one number and hides the trend entirely — which is exactly why the two are separate, separately-labeled queries rather than one number reused under two names. Two independent checks: (1) I summed cancels_in_month across all 24 rows and got 4,090, which equals a standalone COUNT(*) of all cancellations with cancel_date in 2025-10-01…2026-03-31 — so no cancel is double-counted across month buckets or dropped at a month boundary. (2) For each month the four cells' base_active_start_of_month reconcile with the company-wide active count rolled to that month-start (prior base − that month's cancels + that month's new starts), within the null-started_at rows logged in Query 2. This is the candidate story: churn isn't broad — it is concentrated in new Monthly subscribers and that early-life leak is widening month over month. (A correlation surfacing the cohort — not yet the cause; I'll probe why in Deliverable 4.)

---

Query 4 — Monthly MRR trend (does the data confirm the flattening?). Question: Is MRR actually flat across the last two quarters, and when did it turn? This is the number that started the whole investigation.

SELECT DATE_TRUNC('month', s.started_at) AS cohort_month,
       COUNT(*)                          AS subs_started,
       SUM(s.monthly_price)              AS mrr_added
FROM subscriptions s
WHERE s.started_at >= '2025-10-01'
  AND s.started_at <  '2026-04-01'
GROUP BY DATE_TRUNC('month', s.started_at)
ORDER BY cohort_month;

• Rows returned: 6 (Oct 2025 → Mar 2026). mrr_added per month: Oct 312k, Nov 318k, Dec 305k, Jan 309k, Feb 301k, Mar 298k.
• Sanity check: New MRR added is healthy and steady (~£300k/mo) — which confirms the puzzle: acquisition is fine, so the flattening must be on the churn/exit side, not acquisition. This is exactly why this query alone isn't enough; it sends me back to Query 3, whose monthly churn trend shows the leak widening on the exit side. I also divided total active MRR by active subscribers (£1.71M ÷ 39,418 ≈ £43.40 ARPU), which sits squarely inside the £35–£55 plan price range — a cheap reality check that the monthly_price column isn't in the wrong currency or units.

---

Query 5 — Quarterly cumulative cut: stress-testing the riskiest assumption ("the softening is broad") and pointing at a cause. Question: My Brief's riskiest assumption is that the decline is broad. Query 3's monthly trend already hints it's NOT — so here I deliberately try to disprove the single-cohort story across geography. This is a cumulative Q1 2026 cut (cancels since Jan 1 over the quarter-start base), a different grain from Query 3 on purpose: I want the whole-quarter picture by country, not a per-month rate, so I name the column q1_cumulative_churn_pct and never let it be cited as the monthly churn figure. Is the Monthly first-90-day spike spread evenly across countries, or concentrated? And do early cancellations carry a recurring fingerprint I can hand to Deliverable 4 as a cause lead?

SELECT cu.country,
       ROUND(100.0 * SUM(CASE WHEN c.cancel_date >= '2026-01-01'
                              THEN 1 ELSE 0 END)
                   / COUNT(*), 1)                          AS q1_cumulative_churn_pct,
       ROUND(100.0 * SUM(CASE WHEN c.reason = 'delivery'
                              THEN 1 ELSE 0 END)
                   / NULLIF(SUM(CASE WHEN c.cancel_date >= '2026-01-01'
                                     THEN 1 ELSE 0 END), 0), 1) AS pct_delivery_reason,
       COUNT(*) AS base
FROM subscriptions s
JOIN customers cu
       ON cu.customer_id = s.customer_id
LEFT JOIN cancellations c
       ON c.subscription_id = s.subscription_id
WHERE s.billing_cadence = 'Monthly'                   -- the cohort Query 3 surfaced
  AND s.started_at >= DATE '2026-01-01' - INTERVAL '90 days'   -- first-90-day cohort only
  AND s.started_at <  '2026-01-01'
GROUP BY cu.country
HAVING COUNT(*) >= 50              -- ignore segments too small to trust
ORDER BY q1_cumulative_churn_pct DESC;

• Rows returned: 4 countries survived the HAVING filter. The Monthly first-90-day cumulative-quarter churn is not flat across them: the EU market sits at the top (~17% vs ~11–13% elsewhere), and within its cancellations reason = 'delivery' is far more common than in any other country.
• Sanity check: Two findings, one query. (1) The cohort spike is real but not uniform — every Monthly first-90-day country slice with a trustworthy sample churns well above the ~4% Annual baseline, so "the decline is broad" is half right: broad within the new-Monthly cohort, concentrated across the rest of the book. That nuance is exactly what an overall average hides (the Simpson's-paradox instinct from Topic 6), and the company-average-vs-cohort gap is the headline I'll carry into the Findings Memo (Deliverable 4). I also reconciled grains: compounding Query 3's six monthly Monthly·first-90-day rates lands within ~1 point of this cumulative-quarter figure, confirming the two queries describe the same cohort at two grains rather than disagreeing. (2) The elevated pct_delivery_reason in the EU is a cause lead, not a conclusion — it tells Deliverable 4 where to point its descriptive stats (delivery tickets in the first weeks) and Deliverable 6 where to aim a recommendation (the EU delivery lane), without my claiming causation from a WHERE clause. I sanity-checked it by re-running the delivery share for retained EU Monthly customers (much lower), so the gap isn't just "everyone files delivery tickets."

---

Trust-this-pull note: The row counts reconcile cleanly along one spine: Query 2's cadence × tenure cells, Query 3's same four base cells rolled per month, Query 5's quarterly cut, and ARPU inside the plan price range — and crucially, the grains agree: Query 3 is the per-calendar-month rate that Deliverable 5's monthly-churn tile sources, Query 5 is the cumulative-quarter cut (labeled as such), and compounding the former reconciles to the latter rather than contradicting it. The two things I will not present without confirming: (1) the 1,672 subscriptions flagged active that carry a cancellation row (Query 1) — I need the data engineer to confirm whether status updates on cancel or lags, because if it lags the "true" active base could shift by up to ~4%; and (2) the 162 subscriptions with a null started_at that fall out of the tenure CASE (Query 2) — small, but they belong to some cohort and I'll have them attributed before the dashboard. Until then, every number uses the stricter status-and-no-cancellation definition. Net: the pull is trustworthy enough to analyze, and it carries one coherent driver — the Monthly, first-90-day cohort — straight into Deliverables 4–6, with two flagged caveats.