Compute French DAU video-call percentage yesterday
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Compute the percentage of daily active users (DAU) from France who were on at least one video call yesterday (2025-08-31 UTC). DAU is defined as users with any event on 2025-08-31 in events. A user is "from France" if users.country_code = 'FR'. A user counts as having a video call if they appeared as caller or callee in calls with media_type = 'video' and started_at on 2025-08-31 UTC. Return a single row with columns denom_dau_fr, numer_video_callers_fr, pct_video_callers_fr (as a decimal between 0 and 1).
Schemas:
- users(user_id INT, country_code CHAR(2), created_at TIMESTAMP)
- events(event_id INT, user_id INT, event_type TEXT, event_ts TIMESTAMP)
- calls(call_id INT, caller_id INT, callee_id INT, started_at TIMESTAMP, media_type TEXT)
Sample rows (subsets):
users
+---------+--------------+
| user_id | country_code |
+---------+--------------+
| 1 | FR |
| 2 | FR |
| 3 | US |
| 4 | FR |
+---------+--------------+
events
+----------+---------+------------+---------------------+
| event_id | user_id | event_type | event_ts |
+----------+---------+------------+---------------------+
| 501 | 1 | app_open | 2025-08-31 10:00:00 |
| 502 | 2 | search | 2025-08-31 11:00:00 |
| 503 | 3 | app_open | 2025-08-31 12:00:00 |
| 504 | 4 | app_open | 2025-08-31 13:00:00 |
| 505 | 4 | like | 2025-08-30 14:00:00 |
+----------+---------+------------+---------------------+
calls
+---------+-----------+----------+---------------------+------------+
| call_id | caller_id | callee_id| started_at | media_type |
+---------+-----------+----------+---------------------+------------+
| 701 | 1 | 2 | 2025-08-31 15:00:00 | video |
| 702 | 2 | 1 | 2025-08-31 16:00:00 | audio |
| 703 | 4 | 3 | 2025-08-31 17:00:00 | video |
| 704 | 2 | 5 | 2025-09-01 09:00:00 | video |
+---------+-----------+----------+---------------------+------------+
Write a single SQL query; avoid hard-coding user_ids.
Overview: This question evaluates the ability to perform data manipulation and metric computation across relational tables using SQL or Python, specifically measuring daily active users and video-call participation for a geographic subset.
Read the full Meta Data Scientist interview experience this question came from
Using the tables below, compute the percentage of daily active users (DAU) from France who were on at least one video call on 2025-08-31 (UTC).
Definitions:
- A user is "from France" if users.country_code = 'FR'.
- DAU for 2025-08-31 is defined as users who have at least one event in events where event_ts is on 2025-08-31 (UTC), i.e., event_ts >= '2025-08-31 00:00:00' AND event_ts < '2025-09-01 00:00:00'.
- A user counts as having a video call if they appeared as caller or callee in calls with media_type = 'video' and started_at on 2025-08-31 (UTC), i.e., started_at >= '2025-08-31 00:00:00' AND started_at < '2025-09-01 00:00:00'.
Task:
Return a single row with the following columns:
- denom_dau_fr: number of French DAU on 2025-08-31.
- numer_video_callers_fr: number of those French DAU who were on at least one video call on 2025-08-31.
- pct_video_callers_fr: numer_video_callers_fr divided by denom_dau_fr as a decimal between 0 and 1. If there are no French DAU, return 0 for the percentage.
Write a single SQL query to produce this result. Do not hard-code any user_ids.
Tables
users(user_id INT, country_code CHAR(2), created_at TIMESTAMP)
events(event_id INT, user_id INT, event_type VARCHAR(50), event_ts TIMESTAMP)
calls(call_id INT, caller_id INT, callee_id INT, started_at TIMESTAMP, media_type VARCHAR(20))
Hints
- First find French users who are DAU on 2025-08-31 using the events table and users.country_code = 'FR'.
- Unpivot the calls table so that both caller_id and callee_id become user_id, filter to video calls on 2025-08-31, then intersect with the French DAU set and compute the ratio.