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.
Quick Answer: 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.