Analyze Recent User Activity from Video Call Logs
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
1) 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?
Tables
video_calls(caller VARCHAR, recipient VARCHAR, ds DATE, call_id VARCHAR, duration DECIMAL(10,1))
dim_all_users(user_id VARCHAR, age_bucket VARCHAR, country VARCHAR(2), primary_os VARCHAR(20), dau_flag INTEGER, ds DATE)
Hints
- Use COUNT(DISTINCT recipient) per caller for the 7-day metric.
- Filter by ds using CURRENT_DATE; exclude today when defining 'last 7 days'.