cswork / libraryOpen practice workspace

Coding questions · Meta

Write SQL to analyze Group Calls adoption

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.

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.

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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryTEXTNOT NULL

Sample data:

countrysignup_dateuser_id
US2025-06-151
US2025-07-202
CA2025-08-053
US2025-08-104
US2025-08-125

Table experiments

columntypeconstraints
experiment_nameTEXTNOT NULL
user_idINTNOT NULL
variantTEXTNOT NULL, CHECK (variant IN ('treatment','control'))
exposure_dateDATENOT NULL

Sample data:

experiment_nameexposure_dateuser_idvariant
grp_calls_v12025-08-181treatment
grp_calls_v12025-08-182control
grp_calls_v12025-08-193treatment
grp_calls_v12025-08-194control
grp_calls_v12025-08-205treatment

Table calls

columntypeconstraints
call_idINTPRIMARY KEY
started_atTIMESTAMPNOT NULL
is_group_callBOOLEANNOT NULL
creator_user_idINTNOT NULL

Sample data:

call_idcreator_user_idis_group_callstarted_at
1001true2025-08-26 10:00:00
1012false2025-08-26 10:05:00
1023true2025-08-27 08:00:00
1034false2025-08-27 09:00:00

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
joined_atTIMESTAMPNOT NULL
left_atTIMESTAMPNOT NULL

Sample data:

call_idjoined_atleft_atuser_id
1002025-08-26 10:00:002025-08-26 10:30:001
1002025-08-26 10:02:002025-08-26 10:10:002
1002025-08-26 10:04:002025-08-26 10:20:005
1012025-08-26 10:05:002025-08-26 10:25:002
1022025-08-27 08:00:002025-08-27 08:25:003
1022025-08-27 08:03:002025-08-27 08:20:004
1032025-08-27 09:00:002025-08-27 09:10:004

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_user_idINTNOT NULL
since_dateDATENOT NULL

Sample data:

friend_user_idsince_dateuser_id
22025-07-011
52025-08-152
42025-08-123

Expected output

For each country, the number of treatment users exposed on or before 2025-08-24, how many of them started or joined at least one group call between 2025-08-25 and 2025-08-31, and the resulting adoption rate.

adopted_usersadoption_ratecountrytreatment_users
11CA1
21US2

Hints

  1. First identify treatment users with exposure_date <= '2025-08-24' and join them to their countries.
  2. 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).

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryTEXTNOT NULL

Sample data:

countrysignup_dateuser_id
US2025-06-151
US2025-07-202
CA2025-08-053
US2025-08-104
US2025-08-125

Table experiments

columntypeconstraints
experiment_nameTEXTNOT NULL
user_idINTNOT NULL
variantTEXTNOT NULL, CHECK (variant IN ('treatment','control'))
exposure_dateDATENOT NULL

Sample data:

experiment_nameexposure_dateuser_idvariant
grp_calls_v12025-08-181treatment
grp_calls_v12025-08-182control
grp_calls_v12025-08-193treatment
grp_calls_v12025-08-194control
grp_calls_v12025-08-205treatment

Table calls

columntypeconstraints
call_idINTPRIMARY KEY
started_atTIMESTAMPNOT NULL
is_group_callBOOLEANNOT NULL
creator_user_idINTNOT NULL

Sample data:

call_idcreator_user_idis_group_callstarted_at
1001true2025-08-26 10:00:00
1012false2025-08-26 10:05:00
1023true2025-08-27 08:00:00
1034false2025-08-27 09:00:00

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
joined_atTIMESTAMPNOT NULL
left_atTIMESTAMPNOT NULL

Sample data:

call_idjoined_atleft_atuser_id
1002025-08-26 10:00:002025-08-26 10:30:001
1002025-08-26 10:02:002025-08-26 10:10:002
1002025-08-26 10:04:002025-08-26 10:20:005
1012025-08-26 10:05:002025-08-26 10:25:002
1022025-08-27 08:00:002025-08-27 08:25:003
1022025-08-27 08:03:002025-08-27 08:20:004
1032025-08-27 09:00:002025-08-27 09:10:004

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_user_idINTNOT NULL
since_dateDATENOT NULL

Sample data:

friend_user_idsince_dateuser_id
22025-07-011
52025-08-152
42025-08-123

Expected output

A single row summarizing average per-user 1:1 (non-group) call counts for treatment and control in the pre-period and experiment week, their changes, and the difference-in-differences (treatment change minus control change).

control_deltacontrol_exp_avgcontrol_pre_avgdiff_in_difftreatment_deltatreatment_exp_avgtreatment_pre_avg
110-1000

Hints

  1. 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.
  2. 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.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL
countryTEXTNOT NULL

Sample data:

countrysignup_dateuser_id
US2025-06-151
US2025-07-202
CA2025-08-053
US2025-08-104
US2025-08-125

Table experiments

columntypeconstraints
experiment_nameTEXTNOT NULL
user_idINTNOT NULL
variantTEXTNOT NULL, CHECK (variant IN ('treatment','control'))
exposure_dateDATENOT NULL

Sample data:

experiment_nameexposure_dateuser_idvariant
grp_calls_v12025-08-181treatment
grp_calls_v12025-08-182control
grp_calls_v12025-08-193treatment
grp_calls_v12025-08-194control
grp_calls_v12025-08-205treatment

Table calls

columntypeconstraints
call_idINTPRIMARY KEY
started_atTIMESTAMPNOT NULL
is_group_callBOOLEANNOT NULL
creator_user_idINTNOT NULL

Sample data:

call_idcreator_user_idis_group_callstarted_at
1001true2025-08-26 10:00:00
1012false2025-08-26 10:05:00
1023true2025-08-27 08:00:00
1034false2025-08-27 09:00:00

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
joined_atTIMESTAMPNOT NULL
left_atTIMESTAMPNOT NULL

Sample data:

call_idjoined_atleft_atuser_id
1002025-08-26 10:00:002025-08-26 10:30:001
1002025-08-26 10:02:002025-08-26 10:10:002
1002025-08-26 10:04:002025-08-26 10:20:005
1012025-08-26 10:05:002025-08-26 10:25:002
1022025-08-27 08:00:002025-08-27 08:25:003
1022025-08-27 08:03:002025-08-27 08:20:004
1032025-08-27 09:00:002025-08-27 09:10:004

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_user_idINTNOT NULL
since_dateDATENOT NULL

Sample data:

friend_user_idsince_dateuser_id
22025-07-011
52025-08-152
42025-08-123

Expected output

Top calls in the experiment week where both treatment and control users are present among participants (including creators), with counts by variant. Limited to at most 5 calls, ordered by total participants descending, then call_id ascending.

call_idcontrol_participantstotal_participantstreatment_participants
100132
102121

Hints

  1. 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.
  2. 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.