Coding questions · Meta
Analyze Recent User Engagement in Video Calls
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).
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).
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.
Table calls
| column | type | constraints |
|---|---|---|
| caller_id | INTEGER | NOT NULL |
| callee_id | INTEGER | NOT NULL |
| call_start_timestamp | TIMESTAMP | NOT NULL |
| country | VARCHAR(50) | NOT NULL |
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
Sample data:
| call_id | call_start_timestamp | callee_id | caller_id | country |
|---|---|---|---|---|
| 101 | 2025-05-26 14:05:00 | 2 | 1 | France |
| 102 | 2025-05-29 09:20:00 | 3 | 1 | France |
| 103 | 2025-05-30 18:40:00 | 4 | 1 | France |
| 104 | 2025-05-30 12:10:00 | 5 | 2 | Germany |
| 105 | 2025-05-31 07:55:00 | 1 | 3 | France |
Expected output
Considering the fixed 7-day window from 2025-05-26 through 2025-06-01 inclusive, no caller initiated calls with more than three distinct people. Caller 1 has exactly three distinct callees (2, 3, 4); callers 2 and 3 each have one distinct callee. Therefore the metric value is 0.
| metric | value |
|---|---|
| users_over_3_callees_last_7_days | 0 |
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.
Table calls
| column | type | constraints |
|---|---|---|
| caller_id | INTEGER | NOT NULL |
| callee_id | INTEGER | NOT NULL |
| call_start_timestamp | TIMESTAMP | NOT NULL |
| country | VARCHAR(50) | NOT NULL |
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
Sample data:
| call_id | call_start_timestamp | callee_id | caller_id | country |
|---|---|---|---|---|
| 101 | 2025-05-26 14:05:00 | 2 | 1 | France |
| 102 | 2025-05-29 09:20:00 | 3 | 1 | France |
| 103 | 2025-05-30 18:40:00 | 4 | 1 | France |
| 104 | 2025-05-30 12:10:00 | 5 | 2 | Germany |
| 105 | 2025-05-31 07:55:00 | 1 | 3 | France |
Expected output
On 2025-05-31 (yesterday relative to 2025-06-01), the only call in France has caller 3 and callee 1. Thus, the France DAUs for that day are users {1, 3}. By definition, these France DAUs each participated in at least one call that day, so 100% of yesterday’s France DAUs were on at least one video call.
| metric | value |
|---|---|
| pct_yesterdays_fr_daus_on_call | 100 |
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.