Coding questions · Meta
Identify Top Three Active Users by Event Date
This question evaluates proficiency in SQL-based data aggregation and ranking techniques applied to event logs, testing skills such as grouping, counting, and the use of window/ranking functions to identify top users by date.
event_log
+------------+---------+-----------+---------------------+
| event_date | user_id | event_type| event_timestamp |
+------------+---------+-----------+---------------------+
| 2025-06-01 | 123 | click | 2025-06-01 10:15:00 |
| 2025-06-01 | 123 | view | 2025-06-01 10:16:00 |
| 2025-06-01 | 456 | click | 2025-06-01 11:00:00 |
| 2025-06-02 | 123 | click | 2025-06-02 09:00:00 |
| 2025-06-02 | 789 | view | 2025-06-02 12:30:00 |
+------------+---------+-----------+---------------------+
Scenario
Meta onsite interview for a Data Engineer/Data Scientist role; interviewer wants to test SQL depth on product event logs.
Question
Given the event_log table, write a SQL query that returns the three most active users (by event count) for every event_date, ordered by activity.
Hints
Aggregate counts, apply ROW_NUMBER() over each date partition, then filter where row_number <= 3.
Top 3 Most Active Users Per Day
Table event_log
| column | type | constraints |
|---|---|---|
| event_date | DATE | |
| user_id | INTEGER | |
| event_type | VARCHAR | |
| event_timestamp | TIMESTAMP |
Sample data:
| event_date | event_timestamp | event_type | user_id |
|---|---|---|---|
| 2025-06-01 | 2025-06-01 10:15:00 | click | 123 |
| 2025-06-01 | 2025-06-01 10:16:00 | view | 123 |
| 2025-06-01 | 2025-06-01 11:00:00 | click | 456 |
| 2025-06-02 | 2025-06-02 09:00:00 | click | 123 |
| 2025-06-02 | 2025-06-02 12:30:00 | view | 789 |
Expected output
For each event_date, return up to three users with the highest number of events (event_count). Break ties deterministically by user_id ascending. Include columns: event_date, user_id, event_count, activity_rank. Order the output by event_date and activity_rank.
| activity_rank | event_count | event_date | user_id |
|---|---|---|---|
| 1 | 2 | 2025-06-01 | 123 |
| 2 | 1 | 2025-06-01 | 456 |
| 1 | 1 | 2025-06-02 | 123 |
| 2 | 1 | 2025-06-02 | 789 |
Hints
- Aggregate counts per (event_date, user_id).
- Apply ROW_NUMBER() over each event_date partition ordered by event_count DESC (and user_id ASC as a tiebreaker), then filter to row_number <= 3.