Write SQL for initiators and French DAU%
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
users(user_id BIGINT, country VARCHAR(2))
sessions(event_date DATE, user_id BIGINT)
calls(call_id BIGINT, initiator_id BIGINT, started_at TIMESTAMP, is_group_call BOOLEAN)
call_participants(call_id BIGINT, user_id BIGINT)
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.
Tables
users(user_id BIGINT, country VARCHAR(2))
sessions(event_date DATE, user_id BIGINT)
calls(call_id BIGINT, initiator_id BIGINT, started_at TIMESTAMP, is_group_call BOOLEAN)
call_participants(call_id BIGINT, user_id BIGINT)
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.
Community answers
Answer by SS
-- Step 1 calculate denominator using france and sessions table 05/31-- step 2 --> look only at step 1 customers --> chekc for group call and date --> join last two rows and then one more join baswed on coaelsce
With denominator as ( Select a.user_id from users a inner join sessions b on a.user_id = b.user_id where b.event_Date = date '2025-05-31' and a.country = 'FR' ), Callers as (select distinct a.user_id from denominator a inner join (Select a.call_id , a.initiator_id , b.user_id as participants, a.started_At from calls a inner join call_participants b on a.call_id = b.call_id and a.is_group_Call = true and date(a.started_at) = date '2025-05-31') b on (a.user_id = b.initiator_id OR a.user_id = b.participants))
Select count(distinct a.user_id) as dau_france , count(distinct b.user_id) as on_call_france, count(distinct b.user_id) * 100.00/NULLIF(count(distinct a.user_id),0) as pct_on_Call_France, '2025-05-31' as ref_Date from denominator a left join callers b on a.user_id = b.user_id