The Churner Who Came Back

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L5
Asked in
Netflix

Problem

Design a data model for a global subscription business with hundreds of millions of subscribers on several plan tiers across regions, where every upgrade, downgrade, pause, cancel and re-subscribe is kept with the moment it took effect, so a subscriber who comes back still has their earlier cancel on record. Finance and product analytics will use it for churn analysis, plan mix by tier, and the monthly recurring revenue each subscriber was paying, by region, at any point in time.

They cancelled. They came back. The report has to tell both stories correctly.

Worked solution and explanation

What this really is

This is a grain question disguised as a subscription business. The real skill is modeling one person who has several separate spans of being a customer. Anyone can draw a subscribers table and a plans table. The separating move is to declare fact_subscriptions at one row per contiguous subscription period, so a churner who comes back gets a new row, and to keep plan price history in a Type 2 dimension. Update the subscriber's row in place on resubscribe and the win-back overwrites the churn. Last quarter's churn count quietly drops, and the returning customer looks like they never left.

Building it

Step 1: Fix the grain of fact_subscriptions

One row per subscriber per contiguous period, bounded by period_start_ts and a nullable period_end_ts. The row records what kind of change closed it (end_reason) and when each span took effect. mrr_usd is additive across subscribers and tiers at a date. It is not additive across time, so sum it at a point in time, never down a month.

Step 2: Make dim_plans Type 2 on price

monthly_price changes. Each version gets a new plan_sk with effective_from and is_current, and the fact points at the version in effect when the period opened. The link from subscriber to plan tier runs through the period rows, so history never reprices by accident.

Step 3: Keep region on dim_subscribers as Type 2

country is stamped with effective_from, effective_to and is_current. A subscriber who moves gets a new subscriber_sk, so old revenue stays in the region it was earned in. Personal data lives here only, so an erasure request never touches retained financial facts.

Step 4: Give cash its own fact

Charges, refunds, retries and proration live on fact_billing_events at event grain, keyed back to subscription_id, with amount_local and amount_usd. Accrual MRR and billed cash are different questions. One table answering both double-counts every retried payment.

Step 5: Key both facts to a conformed dim_date

Each fact carries its own integer FK: start_date_key on periods, event_date_key on billing events. Finance gets fiscal periods and month ends from one calendar.

`fact_subscriptions` grain: one row per contiguous subscription period per subscriber
  • dim_subscribers: subscriber_sk BIGINT PK, subscriber_id TEXT, country TEXT, effective_from TIMESTAMP, effective_to TIMESTAMP, is_current BOOLEAN
  • dim_plans: plan_sk BIGINT PK, plan_code TEXT, tier TEXT, monthly_price DECIMAL, currency TEXT, effective_from TIMESTAMP, is_current BOOLEAN
  • dim_date: date_key INT PK, calendar_date DATE, fiscal_period TEXT, is_month_end BOOLEAN
  • fact_subscriptions: subscription_id BIGINT PK, subscriber_sk BIGINT FK, plan_sk BIGINT FK, start_date_key INT FK, period_start_ts TIMESTAMP, period_end_ts TIMESTAMP, mrr_usd DECIMAL, end_reason TEXT
  • fact_billing_events: billing_event_id BIGINT PK, subscription_id BIGINT FK, event_date_key INT FK, event_ts TIMESTAMP, event_type TEXT, amount_local DECIMAL, amount_usd DECIMAL
  • fact_subscriptions.subscriber_sk references dim_subscribers.subscriber_sk
  • fact_subscriptions.plan_sk references dim_plans.plan_sk
  • fact_subscriptions.start_date_key references dim_date.date_key
  • fact_billing_events.subscription_id references fact_subscriptions.subscription_id
  • fact_billing_events.event_date_key references dim_date.date_key
Period-grain fact, Type 2 plans
A comeback is a new row and the gap survives. MRR at a date is a filter on period_start_ts and period_end_ts. Old periods keep the plan_sk they were sold at.
One row per subscriber, updated in place
Resubscribing overwrites end_reason, so churn is rewritten after the fact. Win-back analysis has nothing to count, and a price change silently reprices every historical row.
Active subs, MRR and churn by fiscal period and tier
SELECT
    d.fiscal_period,
    p.tier,
    COUNT(DISTINCT s.subscriber_sk) AS active_subs,
    SUM(s.mrr_usd) AS mrr_usd,
    SUM(CASE WHEN s.end_reason = 'voluntary_churn' THEN 1 ELSE 0 END) AS churned
FROM fact_subscriptions s
JOIN dim_plans p ON p.plan_sk = s.plan_sk
JOIN dim_date d ON d.date_key = CAST(TO_CHAR(s.period_start_ts, 'YYYYMMDD') AS INT)
WHERE s.period_start_ts <= d.calendar_date
  AND (s.period_end_ts IS NULL OR s.period_end_ts > d.calendar_date)
GROUP BY d.fiscal_period, p.tier

Rolls subscription periods up through dim_plans and dim_date; the grain makes each output a plain count or sum.

Note COUNT(DISTINCT s.subscriber_sk): a churner who came back inside one fiscal period has two rows, and a plain COUNT(*) would count them twice. Churn is a SUM over end_reason, which works because each period closes exactly once.

Common follow-up questions