Coding questions · Meta
Compute French DAU video-call percentage yesterday
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.
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.
Compute French DAU Video Call Percentage on 2025-08-31
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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country_code | CHAR(2) | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| country_code | created_at | user_id |
|---|---|---|
| FR | 2025-01-10 09:00:00 | 1 |
| FR | 2025-02-15 10:30:00 | 2 |
| US | 2025-03-20 12:00:00 | 3 |
| FR | 2025-04-05 08:45:00 | 4 |
| DE | 2025-05-01 14:15:00 | 5 |
Table events
| column | type | constraints |
|---|---|---|
| event_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
Sample data:
| event_id | event_ts | event_type | user_id |
|---|---|---|---|
| 501 | 2025-08-31 10:00:00 | app_open | 1 |
| 502 | 2025-08-31 11:00:00 | search | 2 |
| 503 | 2025-08-31 12:00:00 | app_open | 3 |
| 504 | 2025-08-31 13:00:00 | app_open | 4 |
| 505 | 2025-08-30 14:00:00 | like | 4 |
| 506 | 2025-08-29 09:00:00 | app_open | 5 |
Table calls
| column | type | constraints |
|---|---|---|
| call_id | INT | PRIMARY KEY, NOT NULL |
| caller_id | INT | NOT NULL |
| callee_id | INT | NOT NULL |
| started_at | TIMESTAMP | NOT NULL |
| media_type | VARCHAR(20) | NOT NULL |
Sample data:
| call_id | callee_id | caller_id | media_type | started_at |
|---|---|---|---|---|
| 701 | 2 | 1 | video | 2025-08-31 15:00:00 |
| 702 | 1 | 2 | audio | 2025-08-31 16:00:00 |
| 703 | 3 | 4 | video | 2025-08-31 17:00:00 |
| 704 | 5 | 2 | video | 2025-09-01 09:00:00 |
Expected output
A single-row result showing: (1) the number of French DAU on 2025-08-31, (2) how many of those French DAU participated in at least one video call on that date, and (3) the resulting fraction as a decimal between 0 and 1.
| denom_dau_fr | numer_video_callers_fr | pct_video_callers_fr |
|---|---|---|
| 3 | 3 | 1 |
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.