The Gift Economy

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 livestream platform where creators go live and viewers drop in and out, fire off chat, and send paid virtual gifts that fund creator payouts. The same broadcast produces viewer visits, chat, and gift purchases at wildly different volumes, and every past gift must keep paying out the exact dollar amount it was worth the moment it was sent, even after finance reprices the gift catalog. Design the data model behind payouts, content recommendations, and engagement analytics.

Someone goes live, thousands tune in, chat explodes, and virtual gifts start flying.

Worked solution and explanation

Why this problem exists in real interviews

Underneath the livestream costume, this is a multi-grain fact modeling problem with one immutable money event hiding inside it. Anyone can list creators, streams, and viewers. What separates candidates is refusing the one big events table: viewer sessions, gifts, and chat live at three different grains and volumes, and the gift row has to snapshot its dollar amount at send time. Miss the per-visit session grain and concurrent-viewer math collapses; miss the snapshot and a repriced gift catalog silently rewrites last month's payouts.

Break down the requirements

Step 1: Separate fact tables by grain

viewer_sessions, gifts, and chat_messages each get their own fact. Chat is 100x the volume of gifts; overloading crushes partition pruning.

Step 2: Model viewer sessions at per-visit grain

A viewer may join and leave a stream five times. Each visit is a row with join_time and leave_time. Per-stream aggregation is a GROUP BY on top.

Step 3: Snapshot gift amounts

gifts.amount_usd is captured at purchase time. If the gift catalog repriced tomorrow, payout math would not change yesterday's numbers.

Step 4: Keep creators and categories as dimensions

creators and stream_categories rarely change per row of fact and are shared across every event type, so they stay conformed dimensions. The receiving creator is always reachable by joining a gift or session through its streams row.

The solution

Below is one defensible model. The anchor is per-visit grain for viewer sessions and snapshotted gift amounts, which keeps both engagement and payouts correct under change.

Gift `amount_usd` is snapshotted at send time to keep creator payouts immutable
  • creators: creator_id BIGINT PK, handle TEXT, country TEXT, joined_at DATE
  • viewers: viewer_id BIGINT PK, handle TEXT, signup_date DATE
  • stream_categories: category_id INT PK, name TEXT
  • streams: stream_id BIGINT PK, creator_id BIGINT FK, category_id INT FK, started_at TIMESTAMP, ended_at TIMESTAMP, status TEXT
  • viewer_sessions: session_id BIGINT PK, viewer_id BIGINT FK, stream_id BIGINT FK, join_time TIMESTAMP, leave_time TIMESTAMP
  • gifts: gift_event_id BIGINT PK, viewer_id BIGINT FK, stream_id BIGINT FK, gift_type TEXT, amount_usd DECIMAL, sent_at TIMESTAMP
  • chat_messages: message_id BIGINT PK, viewer_id BIGINT FK, stream_id BIGINT FK, body TEXT, sent_at TIMESTAMP
  • creators references streams
  • stream_categories references streams
  • streams references viewer_sessions
  • viewers references viewer_sessions
  • streams references gifts
  • viewers references gifts
  • streams references chat_messages
  • viewers references chat_messages

The analysis pattern

Creator payout by stream
SELECT
    c.handle AS creator,
    s.stream_id,
    SUM(g.amount_usd) AS gross_gifts_usd,
    COUNT(DISTINCT vs.viewer_id) AS unique_viewers
FROM streams s
JOIN creators c ON c.creator_id = s.creator_id
LEFT JOIN gifts g ON g.stream_id = s.stream_id
LEFT JOIN viewer_sessions vs ON vs.stream_id = s.stream_id
WHERE s.started_at >= NOW() - INTERVAL '7 days'
GROUP BY c.handle, s.stream_id
ORDER BY gross_gifts_usd DESC

Trade-offs and alternatives

Fact per event type
Separate viewer_sessions, gifts, chat_messages. * Each fact has a clean grain and its own retention * Partition pruning works per workload * Cross-event queries require UNION ALL
Unified events table
One events table with event_type enum. * Simpler ingest * Null-heavy rows and coarse retention * Payout queries scan chat volume for no reason

Common follow-up questions