Write SQL to analyze Group Calls adoption
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write SQL (assume PostgreSQL) to analyze Group Calls adoption and cannibalization. Use this schema and sample data.
Schema:
- users(user_id INT PRIMARY KEY, signup_date DATE, country TEXT)
- experiments(experiment_name TEXT, user_id INT, variant TEXT CHECK (variant IN ('treatment','control')), exposure_date DATE)
- calls(call_id INT PRIMARY KEY, started_at TIMESTAMP, is_group_call BOOLEAN, creator_user_id INT)
- call_participants(call_id INT, user_id INT, joined_at TIMESTAMP, left_at TIMESTAMP)
- friendships(user_id INT, friend_user_id INT, since_date DATE)
Sample rows:
users
+---------+-------------+---------+
| user_id | signup_date | country |
+---------+-------------+---------+
| 1 | 2025-06-15 | US |
| 2 | 2025-07-20 | US |
| 3 | 2025-08-05 | CA |
| 4 | 2025-08-10 | US |
| 5 | 2025-08-12 | US |
+---------+-------------+---------+
experiments
+----------------+---------+----------+---------------+
| experiment_name| user_id | variant | exposure_date |
+----------------+---------+----------+---------------+
| grp_calls_v1 | 1 | treatment| 2025-08-18 |
| grp_calls_v1 | 2 | control | 2025-08-18 |
| grp_calls_v1 | 3 | treatment| 2025-08-19 |
| grp_calls_v1 | 4 | control | 2025-08-19 |
| grp_calls_v1 | 5 | treatment| 2025-08-20 |
+----------------+---------+----------+---------------+
calls
+---------+---------------------+---------------+------------------+
| call_id | started_at | is_group_call | creator_user_id |
+---------+---------------------+---------------+------------------+
| 100 | 2025-08-26 10:00:00 | true | 1 |
| 101 | 2025-08-26 10:05:00 | false | 2 |
| 102 | 2025-08-27 08:00:00 | true | 3 |
| 103 | 2025-08-27 09:00:00 | false | 4 |
+---------+---------------------+---------------+------------------+
call_participants
+---------+---------+---------------------+---------------------+
| call_id | user_id | joined_at | left_at |
+---------+---------+---------------------+---------------------+
| 100 | 1 | 2025-08-26 10:00:00 | 2025-08-26 10:30:00 |
| 100 | 2 | 2025-08-26 10:02:00 | 2025-08-26 10:10:00 |
| 100 | 5 | 2025-08-26 10:04:00 | 2025-08-26 10:20:00 |
| 101 | 2 | 2025-08-26 10:05:00 | 2025-08-26 10:25:00 |
| 102 | 3 | 2025-08-27 08:00:00 | 2025-08-27 08:25:00 |
| 102 | 4 | 2025-08-27 08:03:00 | 2025-08-27 08:20:00 |
| 103 | 4 | 2025-08-27 09:00:00 | 2025-08-27 09:10:00 |
+---------+---------+---------------------+---------------------+
friendships
+---------+----------------+------------+
| user_id | friend_user_id | since_date |
+---------+----------------+------------+
| 1 | 2 | 2025-07-01 |
| 2 | 5 | 2025-08-15 |
| 3 | 4 | 2025-08-12 |
+---------+----------------+------------+
Tasks (use week_of('2025-08-25'..'2025-08-31') as the experiment week; pre-period is '2025-08-18'..'2025-08-24'; only include users exposed on or before 2025-08-24): (1) Adoption: compute, by country, the share of treatment users who either started or joined at least one group call in the experiment week. (2) Cannibalization: compute a difference-in-differences estimate of per-user 1:1 call count (is_group_call=false) between treatment and control (experiment week minus pre-period). (3) Interference check: list top 5 call_ids in the experiment week where participants have mixed variants (both treatment and control present), with counts by variant. Provide SQL for each task and ensure no double-counting of users across calls.
Overview: This question evaluates SQL-based data manipulation and experiment analysis skills, including cohort selection, time-window filtering, joins, aggregations, and difference-in-differences style comparisons using a PostgreSQL event schema.
Group Calls Adoption by Country
Using the schema and sample data below, write a PostgreSQL query to measure adoption of Group Calls.
Consider only users who are in experiment 'grp_calls_v1' and have exposure_date <= '2025-08-24'. Define the experiment week as calls with started_at::date between '2025-08-25' and '2025-08-31' (inclusive).
Compute, by country, for treatment users only:
- The number of treatment users.
- The number of those users who either started or joined at least one group call (is_group_call = true) in the experiment week.
- The share (fraction) of treatment users who adopted Group Calls in that week.
Count each user at most once per country, even if they participate in multiple group calls, and ensure creators who are not explicitly listed as participants are still counted as participating in their calls.
Tables
users(user_id INT, signup_date DATE, country TEXT)
experiments(experiment_name TEXT, user_id INT, variant TEXT, exposure_date DATE)
calls(call_id INT, started_at TIMESTAMP, is_group_call BOOLEAN, creator_user_id INT)
call_participants(call_id INT, user_id INT, joined_at TIMESTAMP, left_at TIMESTAMP)
friendships(user_id INT, friend_user_id INT, since_date DATE)
Hints
- First identify treatment users with exposure_date <= '2025-08-24' and join them to their countries.
- Build a distinct list of users who participated in any group call during '2025-08-25' to '2025-08-31' (including creators) and left join it to treatment users before aggregating by country.
Difference-in-Differences for 1:1 Call Cannibalization
Using the same schema and sample data, compute a difference-in-differences estimate of per-user 1:1 call counts between treatment and control.
Constraints and definitions:
- Only include users in experiment 'grp_calls_v1' with exposure_date <= '2025-08-24'.
- Pre-period: calls with started_at::date between '2025-08-18' and '2025-08-24' (inclusive).
- Experiment week: calls with started_at::date between '2025-08-25' and '2025-08-31' (inclusive).
- Only consider 1:1 calls (is_group_call = false).
- For each user, count distinct 1:1 calls they either started or joined in each period (no double-counting of a user within a call).
Your query should:
1. Compute, for each variant (treatment/control) and each period (pre, experiment week), the average number of 1:1 calls per user.
2. Compute the change (experiment week minus pre-period) for each variant.
3. Output a single row with the treatment and control pre-period averages, experiment-week averages, their changes, and the difference-in-differences: (treatment change - control change).
Tables
users(user_id INT, signup_date DATE, country TEXT)
experiments(experiment_name TEXT, user_id INT, variant TEXT, exposure_date DATE)
calls(call_id INT, started_at TIMESTAMP, is_group_call BOOLEAN, creator_user_id INT)
call_participants(call_id INT, user_id INT, joined_at TIMESTAMP, left_at TIMESTAMP)
friendships(user_id INT, friend_user_id INT, since_date DATE)
Hints
- Start by building user-period combinations (pre and experiment week) for all eligible users, then join in their 1:1 calls via both creators and participants.
- Aggregate to variant-period level to compute total calls and users, then derive per-user averages and finally the difference-in-differences.
Interference Check: Mixed-Variant Calls
Using the same schema and sample data, check for potential interference by identifying calls where participants have mixed variants.
Consider only users in experiment 'grp_calls_v1' with exposure_date <= '2025-08-24', and only calls with started_at::date between '2025-08-25' and '2025-08-31' (inclusive).
Treat all users associated with a call as participants (both the creator_user_id and any rows in call_participants), but do not double-count a user more than once per call.
Write a PostgreSQL query that returns the top 5 calls (by total number of participants, descending) where both treatment and control users are present. For each such call, output:
- call_id
- total_participants
- treatment_participants
- control_participants
Order by total_participants DESC, then call_id ASC, and ensure that counts per call are based on distinct users.
Tables
users(user_id INT, signup_date DATE, country TEXT)
experiments(experiment_name TEXT, user_id INT, variant TEXT, exposure_date DATE)
calls(call_id INT, started_at TIMESTAMP, is_group_call BOOLEAN, creator_user_id INT)
call_participants(call_id INT, user_id INT, joined_at TIMESTAMP, left_at TIMESTAMP)
friendships(user_id INT, friend_user_id INT, since_date DATE)
Hints
- First build a distinct list of users per call (union creators and participants) for calls in the experiment week, then join to experiments to get variants.
- Group by call_id and compute total, treatment, and control counts; finally filter to calls where both treatment and control counts are greater than zero and limit to the top 5 by size.