Coding questions · Meta
Write SQL for initiators and French DAU%
This question evaluates SQL proficiency for time-based event analysis, covering joins, deduplication, CTEs, date/time and timezone handling, and aggregated metric calculation such as DAU and participation rates.
You are given the following PostgreSQL tables. Assume all timestamps are UTC and "today" is 2025-09-01. For any reference to "last 7 days," use the inclusive window 2025-08-26 00:00:00 to 2025-09-01 23:59:59. For any reference to "yesterday," use the date 2025-08-31.
Schema:
- users(user_id bigint primary key, country text)
- sessions(event_date date, user_id bigint) -- one or more rows per user per day are possible
- calls(call_id bigint primary key, initiator_id bigint, started_at timestamp, is_group_call boolean)
- call_participants(call_id bigint, user_id bigint) -- includes the initiator and all other participants
Small sample data:
users
+---------+---------+
| user_id | country |
+---------+---------+
| 1 | FR |
| 2 | FR |
| 3 | US |
| 4 | FR |
| 5 | IN |
sessions
+------------+---------+
| event_date | user_id |
+------------+---------+
| 2025-08-31 | 1 |
| 2025-08-31 | 2 |
| 2025-08-31 | 3 |
| 2025-09-01 | 1 |
| 2025-09-01 | 4 |
calls
+----------+--------------+---------------------+---------------+
| call_id | initiator_id | started_at | is_group_call |
+----------+--------------+---------------------+---------------+
| 10 | 1 | 2025-08-26 09:00:00 | false |
| 11 | 2 | 2025-08-31 21:00:00 | true |
| 12 | 3 | 2025-09-01 01:00:00 | false |
| 13 | 1 | 2025-08-20 12:00:00 | true |
| 14 | 4 | 2025-08-31 23:30:00 | true |
call_participants
+----------+---------+
| call_id | user_id |
+----------+---------+
| 10 | 1 |
| 10 | 3 |
| 11 | 2 |
| 11 | 4 |
| 11 | 5 |
| 12 | 3 |
| 12 | 1 |
| 14 | 2 |
| 14 | 4 |
Tasks: Q1. Return the distinct users who initiated at least one video call (group or 1:1) in the last 7 days (2025-08-26 00:00:00 to 2025-09-01 23:59:59, inclusive). Output columns: user_id. Q2. Compute the percentage of daily active users from France who were on a video call yesterday (2025-08-31). Denominator: distinct users with a sessions.event_date = '2025-08-31' and users.country = 'FR'. Numerator: among those French users, the distinct users who appear in call_participants for calls whose DATE(started_at) = '2025-08-31'. Output columns: ref_date (date), dau_france (int), on_call_france (int), pct_on_call_france (numeric, percentage rounded to 2 decimals). Treat multiple sessions per user correctly (dedupe), and include users who both initiated and participated only once in the numerator. Write a single SQL script with CTEs that answers both Q1 and Q2, and clearly states any timezone/local-date assumptions.
Distinct Initiators in the Last 7 Days
You are given the following PostgreSQL tables. Assume all timestamps are stored in UTC. Treat "today" as 2025-06-01. For any reference to "last 7 days," use the inclusive window from 2025-05-26 00:00:00 to 2025-06-01 23:59:59 (UTC).
Schema:
- users(user_id bigint primary key, country text)
- sessions(event_date date, user_id bigint) -- one or more rows per user per day are possible
- calls(call_id bigint primary key, initiator_id bigint, started_at timestamp, is_group_call boolean)
- call_participants(call_id bigint, user_id bigint) -- includes the initiator and all other participants
Task (Q1): Return the distinct users who initiated at least one video call (group or 1:1) in the last 7 days, i.e., with calls.started_at between '2025-05-26 00:00:00' and '2025-06-01 23:59:59' inclusive, using UTC timestamps. Output columns: user_id.
Table users
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | PRIMARY KEY |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | user_id |
|---|---|
| FR | 1 |
| FR | 2 |
| US | 3 |
| FR | 4 |
| IN | 5 |
Table sessions
| column | type | constraints |
|---|---|---|
| event_date | DATE | NOT NULL |
| user_id | BIGINT | NOT NULL |
Sample data:
| event_date | user_id |
|---|---|
| 2025-05-31 | 1 |
| 2025-05-31 | 2 |
| 2025-05-31 | 3 |
| 2025-06-01 | 1 |
| 2025-06-01 | 4 |
Table calls
| column | type | constraints |
|---|---|---|
| call_id | BIGINT | PRIMARY KEY |
| initiator_id | BIGINT | NOT NULL |
| started_at | TIMESTAMP | NOT NULL |
| is_group_call | BOOLEAN | NOT NULL |
Sample data:
| call_id | initiator_id | is_group_call | started_at |
|---|---|---|---|
| 10 | 1 | false | 2025-05-26 09:00:00 |
| 11 | 2 | true | 2025-05-31 21:00:00 |
| 12 | 3 | false | 2025-06-01 01:00:00 |
| 13 | 1 | true | 2025-05-20 12:00:00 |
| 14 | 4 | true | 2025-05-31 23:30:00 |
Table call_participants
| column | type | constraints |
|---|---|---|
| call_id | BIGINT | NOT NULL |
| user_id | BIGINT | NOT NULL |
Sample data:
| call_id | user_id |
|---|---|
| 10 | 1 |
| 10 | 3 |
| 11 | 2 |
| 11 | 4 |
| 11 | 5 |
| 12 | 3 |
| 12 | 1 |
| 14 | 2 |
| 14 | 4 |
Expected output
One row per distinct user_id who initiated at least one call with started_at between 2025-05-26 00:00:00 and 2025-06-01 23:59:59 UTC. Based on the sample data, calls 10, 11, 12, and 14 are in this window, so initiators 1, 2, 3, and 4 should be returned.
| user_id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
Hints
- Filter calls by the started_at timestamp window using BETWEEN.
- Use DISTINCT on initiator_id to get each user only once.
French DAU on Calls (Percentage for a Given Day)
Using the same PostgreSQL schema as in Question 1, assume all timestamps are stored in UTC and DATE(started_at) is computed in UTC. Treat "yesterday" as the calendar date 2025-05-31.
Schema:
- users(user_id bigint primary key, country text)
- sessions(event_date date, user_id bigint) -- one or more rows per user per day are possible
- calls(call_id bigint primary key, initiator_id bigint, started_at timestamp, is_group_call boolean)
- call_participants(call_id bigint, user_id bigint) -- includes the initiator and all other participants
Task (Q2): Compute the percentage of daily active users from France who were on a video call on 2025-05-31.
- Denominator (dau_france): distinct users such that sessions.event_date = '2025-05-31' AND users.country = 'FR'. Handle multiple sessions per user by deduplicating.
- Numerator (on_call_france): among those French DAU users, the distinct users who appear in call_participants for calls whose DATE(started_at) = '2025-05-31' (UTC day). Include users regardless of whether they initiated or just participated in the call.
- pct_on_call_france: the percentage (on_call_france / dau_france * 100), rounded to 2 decimal places.
Output columns: ref_date (date), dau_france (int), on_call_france (int), pct_on_call_france (numeric, percentage). The result should contain a single row for 2025-05-31. Write a SQL query that uses CTEs to structure the computation.
Table users
| column | type | constraints |
|---|---|---|
| user_id | BIGINT | PRIMARY KEY |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | user_id |
|---|---|
| FR | 1 |
| FR | 2 |
| US | 3 |
| FR | 4 |
| IN | 5 |
Table sessions
| column | type | constraints |
|---|---|---|
| event_date | DATE | NOT NULL |
| user_id | BIGINT | NOT NULL |
Sample data:
| event_date | user_id |
|---|---|
| 2025-05-31 | 1 |
| 2025-05-31 | 2 |
| 2025-05-31 | 3 |
| 2025-06-01 | 1 |
| 2025-06-01 | 4 |
Table calls
| column | type | constraints |
|---|---|---|
| call_id | BIGINT | PRIMARY KEY |
| initiator_id | BIGINT | NOT NULL |
| started_at | TIMESTAMP | NOT NULL |
| is_group_call | BOOLEAN | NOT NULL |
Sample data:
| call_id | initiator_id | is_group_call | started_at |
|---|---|---|---|
| 10 | 1 | false | 2025-05-26 09:00:00 |
| 11 | 2 | true | 2025-05-31 21:00:00 |
| 12 | 3 | false | 2025-06-01 01:00:00 |
| 13 | 1 | true | 2025-05-20 12:00:00 |
| 14 | 4 | true | 2025-05-31 23:30:00 |
Table call_participants
| column | type | constraints |
|---|---|---|
| call_id | BIGINT | NOT NULL |
| user_id | BIGINT | NOT NULL |
Sample data:
| call_id | user_id |
|---|---|
| 10 | 1 |
| 10 | 3 |
| 11 | 2 |
| 11 | 4 |
| 11 | 5 |
| 12 | 3 |
| 12 | 1 |
| 14 | 2 |
| 14 | 4 |
Expected output
A single row summarizing, for 2025-05-31, how many French users were active (had at least one session) and how many of those were on at least one call that day, plus the percentage. From the sample data: French DAU users on 2025-05-31 are user 1 and 2 (dau_france = 2). Calls on 2025-05-31 are call_id 11 and 14. French DAU users participating in these calls: only user 2 (on_call_france = 1). The percentage is 1/2 * 100 = 50.00.
| dau_france | on_call_france | pct_on_call_france | ref_date |
|---|---|---|---|
| 2 | 1 | 50 | 2025-05-31 |
Hints
- First identify French DAU for 2025-05-31 by joining sessions and users and selecting DISTINCT user_id.
- Then find which of those DAU users appear in call_participants for calls whose DATE(started_at) is 2025-05-31, and compute the percentage.