Meta watch-time grain
Avoid the one-fact-per-metric trap by modeling atomic video view events that support watch time by title and unique viewers by device.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
4
Business needs the model must satisfy.
A short-video team asks for two metrics after a feed ranking launch: total watch time by title and unique viewers by device family. A tempting answer is to build one fact for watch time and another for viewers, but the interviewer pushes you to explain the underlying event grain that can serve both.
Meta-style rounds often test whether the candidate can resist metric-shaped tables. Watch time and unique viewers sound like different facts, but both can be computed from the same atomic view event when the grain and dimensional joins are right.
- Create one view-event fact where one row represents one short-video view.
- Keep view id on the fact as a degenerate dimension.
- Add view time and watch-time measure columns.
- Join the fact to viewer, video/title, and device dimensions through explicit surrogate-key foreign keys.
Template
Short-video watch-time starter workspace
The model users start from in the full workspace.
Entities
3
Seeded tables visible in the public preview.
Dialect
postgresql
Generated SQL target for this starter model.
This preview is read-only. Open the workspace to edit and verify; saving needs a free account.
Open workspaceTry the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.