Design a schema for server engagement
Company: xAI
Role: Data Engineer
Category: System Design
Difficulty: medium
Interview Round: Technical Screen
You are building analytics for a chat platform similar to Discord. The raw event sources are:
- `server(server_id, creator_id, created_at)`
- `server_view(server_id, user_id, viewed_at)`
- `server_join(server_id, user_id, joined_at)`
- `message(message_id, server_id, channel_id, user_id, sent_at)`
Tasks:
1. Design a fact-dimension data model that can support analytics on servers, views, joins, and messages.
2. Write SQL to compute the average weekly number of `server_view` events during calendar year 2020.
3. Write SQL to compute the week-over-week change in `server_view` events over the most recent 12 months.
4. Explain how you would handle missing weeks in the time series.
5. Write SQL to return, for every server, the all-time count of server views, server joins, and messages.
Overview: This System Design question evaluates a data engineer's competency in dimensional data modeling, event-driven fact table design, time-series aggregation, and SQL-based analytics within the data warehousing and ETL domain, testing both conceptual understanding of fact-dimension schemas and practical application of SQL for production analytics.