Subscription Commerce SQL Interview Questions

Subscription analytics interviews are not generic SQL screens. They test whether you can write an MRR walk in one query, derive subscription state from an event log, reconcile a snapshot table against a late-arriving event stream, and reason about the difference between voluntary cancellation and involuntary churn. This guide walks through the SQL skills replenishment DTC analysts get asked about, with worked examples on a realistic Athletic Greens-shape schema and a 25-mission path that drills each pattern end to end.

If you are interviewing for a subscription analyst role at a DTC consumables brand like AG1, Hims, Olipop, Magic Spoon, Daily Harvest, or Care/of, the patterns below are the ones that show up in take-home assignments and live whiteboards.

What subscription data actually looks like

Replenishment subscription warehouses are built around two related grains: an event log and a daily snapshot. The event log is canonical — every state change a subscription goes through (created, renewed, skipped, paused, resumed, plan_changed, cancelled, churned) lands as a row in fact_subscription_events. The snapshot table is derived: at the end of every day, the warehouse rolls events forward and emits one row per still-active or paused subscription into fact_subscription_snapshots. The caseSQL Subscription Commerce database models that exact pair — fact_subscription_events (~6,200 rows across 8 event types), fact_subscription_snapshots (~73,000 rows across the last 200 days), and the supporting facts fact_charges, fact_one_time_orders, and fact_subscriptions.

Around the facts sit three dimensions: dim_customers (with a signup_date and an acquisition_channel — paid_social, paid_search, organic, influencer, referral, email), dim_plans (with plan_family — Daily Greens / Recovery Reds / Sleep Stack — and cadence — monthly / quarterly / annual), and dim_skus (per-plan flavor and size variants).

The mental model that interviewers want you to have is straightforward: events are the truth (what actually happened, when), snapshots are the convenience (state on a given day, easier to query). Most subscription SQL questions ask you to choose between the two — and the senior questions ask you to reconcile the two when they disagree, which they sometimes do because of late-arriving events. That gap is where the interview really lives.

Question patterns you’ll be asked

Across replenishment DTC subscription interviews, six patterns dominate. Each one has a distinct SQL shape, and once you can recognize the shape you can almost always write the query. The worked examples below are deliberately adjacent to the exercises in the mission path — same skill, different angle — so you can read the example, then go practice the variant.

The MRR walk

“Walk me through how MRR changed last quarter” is the most-asked question in subscription interviews. The answer is the MRR walk: opening MRR plus new plus expansion minus contraction minus churn plus resurrection equals closing MRR. The skill being tested is decomposing event types into MRR walk components and reconciling against a snapshot. Every senior subscription analyst has written this query at least once.

Worked example, Q1 2026 MRR walk decomposition:

WITH components AS (
  SELECT
    SUM(CASE WHEN event_type = 'created' THEN mrr_delta ELSE 0 END) AS new_mrr,
    SUM(CASE WHEN event_type = 'plan_changed' AND mrr_delta > 0 THEN mrr_delta ELSE 0 END) AS expansion_mrr,
    SUM(CASE WHEN event_type = 'plan_changed' AND mrr_delta < 0 THEN mrr_delta ELSE 0 END) AS contraction_mrr,
    SUM(CASE WHEN event_type IN ('cancelled','churned') THEN mrr_delta ELSE 0 END) AS churn_mrr,
    SUM(CASE WHEN event_type = 'resumed' THEN mrr_delta ELSE 0 END) AS resurrection_mrr
  FROM fact_subscription_events
  WHERE event_ts BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT new_mrr, expansion_mrr, contraction_mrr, churn_mrr, resurrection_mrr FROM components;

Practice this in Mission #16 — same MRR walk decomposition with the full sign-convention treatment, and the path capstone (Mission #25) folds opening and closing MRR into a single-row board-pack output.

Pause-vs-churn forensics

Pause-vs-churn cleanup is the highest-leverage pattern in replenishment subscription analytics, and almost no SQL course teaches it. Real warehouses misclassify 30–40% of voluntary cancels as churn when they’re actually pause-shaped (the customer re-subscribes within 60 days). The skill is a self-join on subscription history at the customer grain, with ROW_NUMBER PARTITION BY customer_id to pick the immediate next subscription after a cancel.

Worked example, finding the pause-shaped cohort:

WITH cancels AS (
  SELECT subscription_id AS original_sub_id, customer_id, event_ts AS cancel_date
  FROM fact_subscription_events WHERE event_type = 'cancelled'
),
creates AS (
  SELECT subscription_id AS new_sub_id, customer_id, event_ts AS create_date
  FROM fact_subscription_events WHERE event_type = 'created'
),
paired AS (
  SELECT c.original_sub_id, cr.new_sub_id, c.cancel_date, cr.create_date,
         (cr.create_date::date - c.cancel_date::date) AS gap_days,
         ROW_NUMBER() OVER (PARTITION BY c.original_sub_id ORDER BY cr.create_date) AS rn
  FROM cancels c
  JOIN creates cr ON cr.customer_id = c.customer_id AND cr.create_date > c.cancel_date
)
SELECT * FROM paired WHERE rn = 1 AND gap_days <= 60 ORDER BY gap_days;

Practice this in Mission #20 — the misclassification cleanup that the lifecycle team rescues with re-onboarding interventions.

Cohort retention from events

Cohort retention questions test whether you can pick a subscription’s state at an arbitrary date by replaying its event history. Snapshots only span the last 6–12 months of warehouse history; events are forever. So the senior version of cohort retention always uses events. The skill is a correlated subquery (or a window function) that finds the latest event before month-N and maps the event_type to active / paused / cancelled / churned.

Worked example, 90-day retention from events:

SELECT DATE_TRUNC('month', s.started_at::date) AS cohort_month,
       COUNT(*) AS cohort_size,
       SUM(CASE WHEN
         (SELECT event_type FROM fact_subscription_events e
            WHERE e.subscription_id = s.subscription_id
              AND e.event_ts <= (s.started_at::date + INTERVAL '90 days')::text
            ORDER BY e.event_ts DESC LIMIT 1
         ) IN ('created','renewed','skipped','resumed') THEN 1 ELSE 0 END) AS m3_active
FROM fact_subscriptions s
WHERE s.started_at <= '2025-03-31'
GROUP BY DATE_TRUNC('month', s.started_at::date)
ORDER BY cohort_month;

Practice this in Mission #21 — the full 12-month decay curve with m3 / m6 / m12 active counts per cohort.

Snapshot-vs-event reconciliation

The snapshot-vs-event drift pattern shows up on diligence-grade interviews. Late-arriving events — an event with event_ts on day X but posted_at on day X+10 — cause the snapshot table generated on day X+5 to disagree with the event-derived state at day X+5. The skill is filtering events on both event_ts ≤ snapshot_date and posted_at ≤ snapshot_date, replaying state, and comparing.

Worked example, finding late-arriving events:

SELECT subscription_id, event_type, event_ts, posted_at,
       (posted_at::date - event_ts::date) AS gap_days
FROM   fact_subscription_events
WHERE  posted_at > event_ts
ORDER  BY gap_days DESC, subscription_id ASC;

Practice this in Mission #13 — the introduction; Mission #23 is the full multi-CTE drift report ranked by impact.

Net Revenue Retention by cohort

NRR is the metric every subscription investor asks about. For each signup-month cohort: ratio of current MRR (sum of mrr from latest active snapshot, joined to customers in the cohort) to initial MRR (sum of mrr_delta from created events for customers in the cohort). 100%+ means cohorts are net-expanding; sub-100% means they’re net-contracting. Replenishment DTC typically lands in the 30–60% range at 12-month maturity, so don’t panic when your output isn’t SaaS-grade.

Worked example, NRR by signup cohort:

WITH initial_mrr AS (
  SELECT DATE_TRUNC('month', event_ts::date) AS cohort_month, SUM(mrr_delta) AS initial_mrr
  FROM fact_subscription_events WHERE event_type = 'created'
  GROUP BY DATE_TRUNC('month', event_ts::date)
),
current_mrr AS (
  SELECT DATE_TRUNC('month', c.signup_date::date) AS cohort_month, SUM(s.mrr) AS current_mrr
  FROM fact_subscription_snapshots s
  JOIN dim_customers c ON c.customer_id = s.customer_id
  WHERE s.snapshot_date = '2026-03-31' AND s.state = 'active'
  GROUP BY DATE_TRUNC('month', c.signup_date::date)
)
SELECT i.cohort_month, i.initial_mrr, COALESCE(c.current_mrr, 0) AS current_mrr,
       ROUND(100.0 * COALESCE(c.current_mrr, 0) / NULLIF(i.initial_mrr, 0), 1) AS nrr_pct
FROM initial_mrr i LEFT JOIN current_mrr c USING (cohort_month)
WHERE i.cohort_month < '2025-04-01'
ORDER BY i.cohort_month;

Practice this in Mission #18 — mature cohorts only, 12-month maturity threshold, full NRR percent computed with NULLIF guard.

Subscriber-vs-one-time blended LTV

Hybrid one-time + subscription LTV is the question every modern DTC operator answers in their first quarter. For an acquired cohort, split into subscribers (any successful charge) and one-time-only (zero charges, but one-time orders), compute average LTV for each, and present the multiple. A 3:1 multiple justifies the subscription motion’s CAC; below 2:1 starts a board conversation about whether the subscription product-fit is right.

Worked example, 2024-Q3 cohort split:

WITH cohort AS (
  SELECT customer_id FROM dim_customers
  WHERE signup_date BETWEEN '2024-07-01' AND '2024-09-30'
),
sub_rev AS (
  SELECT c.customer_id, COALESCE(SUM(ch.amount), 0) AS sub_revenue
  FROM cohort c LEFT JOIN fact_charges ch
    ON ch.customer_id = c.customer_id AND ch.status = 'succeeded'
  GROUP BY c.customer_id
),
one_time AS (
  SELECT c.customer_id, COALESCE(SUM(o.total_amount), 0) AS one_time_revenue
  FROM cohort c LEFT JOIN fact_one_time_orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
)
SELECT CASE WHEN s.sub_revenue > 0 THEN 'subscriber'
            WHEN o.one_time_revenue > 0 THEN 'one_time_only' END AS cohort,
       COUNT(*) AS customer_count,
       ROUND(AVG(s.sub_revenue + o.one_time_revenue)::numeric, 2) AS avg_ltv
FROM sub_rev s JOIN one_time o USING (customer_id)
WHERE s.sub_revenue > 0 OR o.one_time_revenue > 0
GROUP BY 1 ORDER BY avg_ltv DESC;

Practice this in Mission #17 — the matched-cohort framing with explicit CASE bucketing. Mission #24 extends the same skill across all mature cohorts for the board pack.

Gotchas that cost you the offer

Six mistakes show up reliably on subscription analytics screens. They’re the difference between a candidate who has “done some SQL” and a candidate who has worked on a real subscription warehouse.

The 25-mission curriculum

The path is structured to walk from snapshot-table fundamentals (free tier, M1–M10) through event-grain pipelines (Hard tier, M11–M15), the unit-economic constructions every CFO ships (Expert tier, M16–M20), and the multi-CTE capstones every senior subscription analyst writes (Master tier, M21–M25). M25 is THE MRR walk in a single multi-CTE query — the metric every subscription interview screens on.

Frequently asked