The Long Play

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 movie streaming platform and need a data model for our analytics warehouse. The catalog includes both standalone movies and multi-episode series, every title can be tagged with more than one genre, and a user's subscription tier can change over time. We want to understand what people watch, how they engage, and which content drives subscriptions. Design the schema.

They pressed play. What happened next is the whole question.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate can model a content hierarchy (title down to episode) alongside a viewing fact at session grain and express a many-to-many genre relationship correctly. Subscription temporality is the stealth difficulty: churn and upgrade tracking need effective-dated rows, not a single status column.

Break down the requirements

Step 1: Build the content hierarchy

titles (movies and series), episodes for series content. A movie is a title without episodes; a series is a title with many. Season and episode number are attributes on each episode.

Step 2: Declare the viewing grain

viewing_sessions is one row per play: user, content, start_time, duration, percent_watched. Per-title aggregation sits on top.

Step 3: Model genres as M:N

A title can be Sci-Fi and Drama. title_genres is the junction, keyed by (title_id, genre_id). A single FK cannot express this and forces nonsense compromises.

Step 4: Model subscriptions temporally

subscriptions has start_date, end_date, and tier. A user upgrading from standard to premium is a new row, not an UPDATE.

The solution

Below is one defensible model. The anchor is per-session grain on viewing_sessions plus a clean M:N for genres; the rest of the schema falls out.

`title_genres` as an M:N junction and subscriptions with `start_date`/`end_date` for temporal tier tracking
  • users: user_id BIGINT PK, email TEXT, country TEXT, signup_date DATE
  • subscriptions: subscription_id BIGINT PK, user_id BIGINT FK, tier TEXT, start_date DATE, end_date DATE
  • titles: title_id BIGINT PK, name TEXT, kind TEXT, release_year INT
  • episodes: episode_id BIGINT PK, title_id BIGINT FK, season_number INT, episode_number INT, runtime_minutes INT
  • genres: genre_id INT PK, name TEXT
  • title_genres: title_id BIGINT FK, genre_id INT FK
  • viewing_sessions: session_id BIGINT PK, user_id BIGINT FK, title_id BIGINT FK, episode_id BIGINT FK, start_time TIMESTAMP, duration_sec INT, percent_watched DECIMAL
  • users references subscriptions
  • titles references episodes
  • titles references title_genres
  • genres references title_genres
  • users references viewing_sessions
  • titles references viewing_sessions
  • episodes references viewing_sessions

The analysis pattern

Hours watched by genre in the last 30 days
SELECT
    g.name AS genre,
    SUM(vs.duration_sec) / 3600.0 AS hours_watched,
    COUNT(DISTINCT vs.user_id) AS unique_viewers
FROM viewing_sessions vs
JOIN title_genres tg ON tg.title_id = vs.title_id
JOIN genres g ON g.genre_id = tg.genre_id
WHERE vs.start_time >= NOW() - INTERVAL '30 days'
GROUP BY g.name
ORDER BY hours_watched DESC

Trade-offs and alternatives

Session fact + M:N genres
viewing_sessions at per-play grain. * Engagement metrics at any granularity * Recommendation features are immediate * Full fidelity comes at storage cost
Rolled-up daily fact
A user_title_daily table pre-aggregated per user per title per day. * Cheap for dashboards * Loses session-level signal * Recommendations need another pipeline

Common follow-up questions