Identify Top Three Active Users by Event Date
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Quick Answer: 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.