Problem Description
Design a data model for Discord video call analytics. Video call activity is captured from both client-side and server-side logs.
The data model must support reliable analytics using at least the following information:
- Video/call identifier
- User identifier
- User country
- Call duration
- Number of joins
- Number of rejoins
- Event timestamp
Assume the following:
- A single video call can involve multiple users.
- A single video call may generate multiple raw events from various sources (client-side, server-side).
Deliverables
- Data Model Design: Explain how you would model:
- Raw events
- Cleaned fact tables
- User dimension tables
- Country dimension tables
- SQL Queries: Write SQL for the following analytics questions:
- For each country and calendar day, compute the total number of video calls.
- For every day present in the database, compute for each country the number of video calls in the trailing 7-day, 14-day, and 30-day windows ending on that day.
Assumptions
Clearly state any assumptions made, particularly regarding how to count a video call that includes users from multiple countries.
AI interview prep
Core dilemma
Accurately aggregating complex, multi-source, multi-user event data into reliable, de-duplicated analytics while handling late-arriving or inconsistent data, and efficiently querying time-series windows at scale.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Problem Description
Design a data model for Discord video call analytics. Video call activity is captured from both client-side and server-side logs.
The data model must support reliable analytics using at least the following information:
- Video/call identifier
- User identifier
- User country
- Call duration
- Number of joins
- Number of rejoins
- Event timestamp
Assume the following:
- A single video call can involve multiple users.
- A single video call may generate multiple raw events from various sources (client-side, server-side).
Deliverables
- Data Model Design: Explain how you would model:
- Raw events
- Cleaned fact tables
- User dimension tables
- Country dimension tables
- SQL Queries: Write SQL for the following analytics questions:
- For each country and calendar day, compute the total number of video calls.
- For every day present in the database, compute for each country the number of video calls in the trailing 7-day, 14-day, and 30-day windows ending on that day.
Assumptions
Clearly state any assumptions made, particularly regarding how to count a video call that includes users from multiple countries.