Coding questions · Meta
Analyze Recent User Activity from Video Call Logs
This question evaluates proficiency in data manipulation and aggregation over event logs and user dimension tables, assessing skills in joining datasets, measuring unique interactions, and computing recent user-activity metrics using SQL or Python.
video_calls
caller | recipient | ds | call_id | duration
123 | 456 | 2019-01-01 | 4325 | 864.4
032 | 789 | 2019-01-01 | 9395 | 263.7
456 | 032 | 2019-01-01 | 0879 | 22.0
dim_all_users
user_id | age_bucket | country | primary_os | dau_flag | ds
123 | 25-34 | us | android | 1 | 2019-07-01
456 | 65+ | gb | ios | 1 | 2019-07-01
789 | 13-17 | fr | ios | 0 | 2019-07-01
032 | 45-54 | eg | android | 1 | 2019-07-01
Scenario
Instagram wants quick insights from their video call logs to understand recent user activity.
Question
How many users started a call with more than 3 different people in the last 7 days? 2. What percentage of yesterday’s daily-active French users were on at least one video call?
Hints
COUNT DISTINCT correctly, filter by ds using current_date, treat caller and recipient symmetrically for usage rate.