Meta messaging engagement
Start from DAU and retention metrics, then design the event fact and user/thread dimensions that make those metrics reliable.
Concept
fact-table-grain
The primary modeling idea this problem reinforces.
Requirements
4
Business needs the model must satisfy.
A messaging product team sees DAU flattening while sent-message volume keeps rising. The interviewer asks you to define the engagement metrics first, then design a model that can explain whether users are sending more messages, reading more threads, or churning after their first conversation.
Meta-style modeling prompts are product conversations before they are schema exercises. If the model skips event grain or hides thread context, DAU and retention become dashboard numbers without a path to debug product behavior.
- Create a message-event fact where one row represents one observed engagement event.
- Keep message id queryable on the fact as a degenerate dimension.
- Add sent/event time and event type fields that support DAU, retention, sends, reads, and reactions.
- Connect the fact to user and thread dimensions through explicit surrogate-key foreign keys.
Template
Messaging engagement starter workspace
The model users start from in the full workspace.
Entities
2
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.