Analyze Recent User Engagement in Video Calls
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
calls
+-----------+-----------+---------------------+---------+---------+
| caller_id | callee_id | call_start_timestamp| country | call_id |
+-----------+-----------+---------------------+---------+---------+
| 1 | 2 | 2023-03-10 14:05:00 | France | 101 |
| 1 | 3 | 2023-03-12 09:20:00 | France | 102 |
| 1 | 4 | 2023-03-13 18:40:00 | France | 103 |
| 2 | 5 | 2023-03-13 12:10:00 | Germany | 104 |
| 3 | 1 | 2023-03-14 07:55:00 | France | 105 |
##### Scenario
Video-call product analytics team wants quick health checks on recent engagement.
##### Question
How many users started a call with more than three different people in the last seven days?
What percentage of yesterday’s DAUs located in France were on at least one video call?
##### Hints
Think window functions, DISTINCT callee counts, filtering by DATE(call_start_timestamp).
Overview: This question evaluates competency in data manipulation and product analytics, focusing on transforming event-level call records into user-level engagement metrics using SQL or Python; the category is Data Manipulation (SQL/Python).
Users with >3 Callees in Last 7 Days (Explicit Dates)
Using the calls table, compute how many distinct users started calls with more than three different people between 2025-05-26 and 2025-06-01 (inclusive). Return a single row with the metric name and the count.
Tables
calls(caller_id INTEGER, callee_id INTEGER, call_start_timestamp TIMESTAMP, country VARCHAR(50), call_id INTEGER)
Hints
- Filter calls to the date range '2025-05-26' through '2025-06-01' using DATE(call_start_timestamp).
- Group by caller_id, count DISTINCT callee_id, then count how many callers have distinct_callees > 3.
Percentage of Yesterday’s France DAUs on Calls (Explicit Date)
Assume 'today' is 2025-06-01, so 'yesterday' is 2025-05-31. Using the calls table, compute the percentage of yesterday’s DAUs located in France who were on at least one video call yesterday (2025-05-31). A user is considered located in France if they appear in any call row where country = 'France' on 2025-05-31, and a DAU is a user who participated in at least one call that day. Return a single row with the metric name and the percentage value.
Tables
calls(caller_id INTEGER, callee_id INTEGER, call_start_timestamp TIMESTAMP, country VARCHAR(50), call_id INTEGER)
Hints
- Treat yesterday as the fixed date '2025-05-31' and filter using DATE(call_start_timestamp).
- Collect unique participants (caller or callee) in rows with country = 'France' on that date; by the given DAU definition, all such users were on at least one call.