Katkat
Back

Design Video Call Analytics Tables

DiscordData Engineer · Onsite
HardData EngineerOnsiteSystem Design

Last updated

This question evaluates data modeling and analytics competencies including event schema design, dimensional modeling (facts and dimensions), ETL/cleaning and deduplication, and time-windowed SQL aggregation.

You are building analytics tables for Discord video calls. Video-call activity is recorded from both client-side and server-side logs.

Design a data model that supports 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 a call can involve multiple users, and one call may generate multiple raw events. Explain how you would model raw events, cleaned facts, and user/country dimensions.

Then write SQL for the following analytics questions:

  1. For each country and calendar day, compute the number of video calls.
  2. For every day that appears 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.

State any assumptions you make, especially how you count a call that includes users from multiple countries.