When the Music Stops

A medium 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
medium
Seniority
L5

Problem

We run a music streaming service and our premium subscribers are cancelling at 8% a month. Design a data model that lets the retention team compare how listening habits, playlist activity, and subscription history differ between members who churned and those who stayed in the weeks before a cancellation.

Subscribers are leaving. The data knows why.

Worked solution and explanation

Why this problem exists in real interviews

Churn modeling probes whether a candidate can coexist three different grains cleanly: a lifecycle event log, a pre-aggregated daily snapshot, and an atomic play-event fact. The signal is whether the candidate understands when to pay the storage cost of pre-aggregation versus reconstructing daily engagement on every query.

Break down the requirements

Step 1: Lifecycle as an event log

fact_subscription_events captures signup, upgrade, downgrade, pause, cancel, reactivate. Append-only, one row per state change, event_type as a discriminator.

Step 2: Daily engagement pre-aggregated

fact_daily_engagement is one row per user per day with rolled-up sessions, minutes, distinct tracks, and playlist activity (playlists created, social shares). This is the table most dashboards hit; it pays for itself the first time a cohort analysis runs, and it is the only table that carries the playlist signal the churn analysis asks for. The specific columns are a choice, not a mandate: any sensible mix of daily listening volume and playlist measures works.

Step 3: Atomic play events for deep dives

fact_play_events is the raw source. It feeds the listening columns of fact_daily_engagement and stays available for content-level analytics, A/B tests, and anomaly investigations.

Step 4: Conform dim_users and dim_content

Both dimensions are shared across all three facts. Conformity makes "did users who cancelled last month listen less and stop building playlists in week 3" a single query.

The solution

Below is one defensible approach. The coexistence of three grains is the anchor: lifecycle, daily, and atomic each serve different query patterns without collapsing into one table.

`fact_daily_engagement` is a pre-aggregated snapshot carrying listening and playlist signals; `fact_play_events` is the atomic source; `fact_subscription_events` is the lifecycle log
  • dim_users: user_sk BIGINT PK, user_id TEXT, signup_date DATE, country TEXT, plan_tier TEXT
  • dim_content: content_sk BIGINT PK, content_id TEXT, title TEXT, genre TEXT, release_date DATE
  • fact_subscription_events: event_id BIGINT PK, user_sk BIGINT FK, event_type TEXT, event_timestamp TIMESTAMP, plan_tier TEXT
  • fact_daily_engagement: engagement_date DATE, user_sk BIGINT FK, sessions INT, minutes_played INT, distinct_tracks INT, playlists_created INT, social_shares INT
  • fact_play_events: play_event_id BIGINT PK, user_sk BIGINT FK, content_sk BIGINT FK, started_at TIMESTAMP, play_seconds INT, skip BOOLEAN
  • dim_users references fact_subscription_events
  • dim_users references fact_daily_engagement
  • dim_users references fact_play_events
  • dim_content references fact_play_events

The analysis pattern

Seven-day engagement before cancellation
WITH cancels AS (
    SELECT user_sk, event_timestamp::date AS cancel_date
    FROM fact_subscription_events
    WHERE event_type = 'cancel'
      AND event_timestamp >= '2025-01-01'
)
SELECT
    u.plan_tier,
    u.country,
    AVG(e.minutes_played) AS avg_daily_minutes,
    AVG(e.distinct_tracks) AS avg_daily_tracks,
    AVG(e.playlists_created) AS avg_daily_playlists,
    COUNT(DISTINCT c.user_sk) AS cancelled_users
FROM cancels c
JOIN dim_users u ON u.user_sk = c.user_sk
JOIN fact_daily_engagement e
    ON e.user_sk = c.user_sk
   AND e.engagement_date BETWEEN c.cancel_date - INTERVAL '7 days' AND c.cancel_date - INTERVAL '1 day'
GROUP BY u.plan_tier, u.country
ORDER BY avg_daily_minutes

Trade-offs and alternatives

Three-grain layered model
Fast dashboards, atomic detail when needed, idempotent rebuilds of the daily listening columns, and a home for playlist/social signals that play events cannot reconstruct. Cost: ETL must handle backfills correctly and storage footprint is roughly 1.01x the atomic fact alone.
Atomic fact only
Single source of truth, no derivation pipeline. Cost: every daily roll-up query scans the full play-event fact, dashboards slow proportionally with listen volume, and playlist/social activity has nowhere to live.

Common follow-up questions