cswork / libraryOpen practice workspace

Coding questions · Meta

Compute video-call SQL metrics with edge cases

This question evaluates the ability to manipulate time-series and relational call/event data using SQL (and optionally Python), emphasizing aggregation, DISTINCT counting, interval-overlap logic, and handling edge cases such as duplicates, failed calls, users joining multiple times, calls spanning midnight, and test accounts.

Use 'today' = 2025-09-01. Assume UTC timestamps. Write SQL to answer both parts below and call out how your queries handle edge cases (duplicates, failed calls, users joining multiple times, calls spanning midnight, test accounts). Schema and small sample data are provided.

Schema:

  • users(user_id INT PK, country_code CHAR(2), created_at DATE, is_test BOOL)
  • calls(call_id INT PK, initiator_user_id INT, call_type ENUM('video','audio'), status ENUM('completed','missed','failed'), started_at TIMESTAMP, ended_at TIMESTAMP)
  • call_participants(call_id INT, user_id INT, role ENUM('initiator','callee'), joined_at TIMESTAMP, left_at TIMESTAMP)
  • events(event_id INT PK, user_id INT, event_type VARCHAR, event_ts TIMESTAMP)

Sample tables (subset):

users
user_id | country_code | created_at | is_test
1 | FR | 2025-07-01 | false
2 | FR | 2025-08-10 | false
3 | US | 2025-06-20 | false
4 | FR | 2025-08-25 | false
5 | DE | 2025-08-28 | false

calls
call_id | initiator_user_id | call_type | status | started_at | ended_at
10 | 1 | video | completed | 2025-08-30 10:00:00 | 2025-08-30 10:30:00
11 | 1 | video | failed | 2025-08-31 09:00:00 | 2025-08-31 09:05:00
12 | 2 | video | completed | 2025-09-01 12:00:00 | 2025-09-01 12:20:00
13 | 3 | audio | completed | 2025-08-31 13:00:00 | 2025-08-31 13:10:00

call_participants
call_id | user_id | role | joined_at | left_at
10 | 1 | initiator | 2025-08-30 10:00:00 | 2025-08-30 10:30:00
10 | 2 | callee | 2025-08-30 10:02:00 | 2025-08-30 10:30:00
10 | 4 | callee | 2025-08-30 10:05:00 | 2025-08-30 10:20:00
11 | 1 | initiator | 2025-08-31 09:00:00 | 2025-08-31 09:01:00
11 | 3 | callee | 2025-08-31 09:00:00 | 2025-08-31 09:00:10
12 | 2 | initiator | 2025-09-01 12:00:00 | 2025-09-01 12:20:00
12 | 3 | callee | 2025-09-01 12:00:05 | 2025-09-01 12:20:00
13 | 3 | initiator | 2025-08-31 13:00:00 | 2025-08-31 13:10:00
13 | 5 | callee | 2025-08-31 13:00:05 | 2025-08-31 13:10:00

events
event_id | user_id | event_type | event_ts
100 | 1 | app_open | 2025-08-31 08:55:00
101 | 2 | app_open | 2025-08-31 09:10:00
102 | 3 | app_open | 2025-08-31 13:00:00
103 | 4 | app_open | 2025-08-31 20:00:00

Tasks: A) How many distinct users initiated video calls with more than 3 different other users during the last 7 days inclusive of today, i.e., 2025-08-26 00:00:00 through 2025-09-01 23:59:59? Count callees across all video calls they started in that window; exclude the initiator themself; ignore test accounts; include only calls with status='completed'. Return a single integer. B) What percentage of DAUs from France were on a video call yesterday (2025-08-31)? Define DAU_FR as distinct users with users.country_code='FR', is_test=false, who generated any events on 2025-08-31 (events.event_ts in [2025-08-31 00:00:00, 2025-08-31 23:59:59]). Define ONCALL_FR as distinct users in FR who participated in any video call (initiator or callee) with any overlap with 2025-08-31 (interval overlap between [joined_at, left_at] and the day). Use status='completed'. Return a single row with numerator, denominator, and percentage to two decimals. Also provide a version that safeguards against double-counting users who join multiple times in the same call.

Initiators with >3 distinct callees (completed video calls, last 7 days)

Assume UTC timestamps and use the explicit window 2025-08-26 00:00:00 through 2025-09-01 23:59:59 (inclusive).

Return a single integer: the number of DISTINCT users who initiated video calls where, across ALL video calls they initiated in this window, they interacted with MORE THAN 3 DISTINCT other users (callees).

Rules:

  • Only include calls with call_type='video' AND status='completed'.
  • Count unique callees across all qualifying calls started by the initiator within the window.
  • Exclude the initiator themself from the callee count.
  • Ignore test accounts (users.is_test = true) both as initiators and as callees.
  • Your logic should be robust to duplicates in call_participants (e.g., a user joins the same call multiple times).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
country_codeCHAR(2)NOT NULL
created_atDATENOT NULL
is_testBOOLEANNOT NULL

Sample data:

country_codecreated_atis_testuser_id
FR2025-07-01false1
FR2025-08-10false2
US2025-06-20false3
FR2025-08-25false4
DE2025-08-28false5
FR2025-08-01true6
ES2025-07-15false7
IT2025-08-05false8
GB2025-08-12false9
FR2025-08-15false10

Table calls

columntypeconstraints
call_idINTPRIMARY KEY, NOT NULL
initiator_user_idINTNOT NULL
call_typeVARCHAR(10)NOT NULL
statusVARCHAR(10)NOT NULL
started_atTIMESTAMPNOT NULL
ended_atTIMESTAMPNOT NULL

Sample data:

call_idcall_typeended_atinitiator_user_idstarted_atstatus
10video2025-08-30 10:30:0012025-08-30 10:00:00completed
11video2025-08-31 09:05:0012025-08-31 09:00:00failed
12video2025-09-01 12:20:0022025-09-01 12:00:00completed
13audio2025-08-31 13:10:0032025-08-31 13:00:00completed
14video2025-09-01 00:10:0012025-08-31 23:50:00completed
15video2025-08-27 14:10:0012025-08-27 14:00:00completed
16video2025-08-28 15:15:0012025-08-28 15:00:00completed
17video2025-08-29 16:20:0012025-08-29 16:00:00completed
18video2025-08-31 09:45:0022025-08-31 09:30:00completed

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
roleVARCHAR(10)NOT NULL
joined_atTIMESTAMPNOT NULL
left_atTIMESTAMPNOT NULL

Sample data:

call_idjoined_atleft_atroleuser_id
102025-08-30 10:00:002025-08-30 10:30:00initiator1
102025-08-30 10:02:002025-08-30 10:30:00callee2
102025-08-30 10:03:002025-08-30 10:05:00callee2
102025-08-30 10:05:002025-08-30 10:20:00callee4
112025-08-31 09:00:002025-08-31 09:01:00initiator1
112025-08-31 09:00:002025-08-31 09:00:10callee3
122025-09-01 12:00:002025-09-01 12:20:00initiator2
122025-09-01 12:00:052025-09-01 12:20:00callee3
132025-08-31 13:00:002025-08-31 13:10:00initiator3
132025-08-31 13:00:052025-08-31 13:10:00callee5
142025-08-31 23:50:002025-09-01 00:10:00initiator1
142025-08-31 23:51:002025-09-01 00:10:00callee3
152025-08-27 14:00:002025-08-27 14:10:00initiator1
152025-08-27 14:00:102025-08-27 14:10:00callee7
162025-08-28 15:00:002025-08-28 15:15:00initiator1
162025-08-28 15:00:052025-08-28 15:15:00callee8
162025-08-28 15:01:002025-08-28 15:05:00callee6
172025-08-29 16:00:002025-08-29 16:20:00initiator1
172025-08-29 16:00:072025-08-29 16:20:00callee9
182025-08-31 09:30:002025-08-31 09:45:00initiator2
182025-08-31 09:31:002025-08-31 09:40:00callee4
182025-08-31 09:41:002025-08-31 09:45:00callee4

Table events

columntypeconstraints
event_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
event_typeVARCHAR(50)NOT NULL
event_tsTIMESTAMPNOT NULL

Sample data:

event_idevent_tsevent_typeuser_id
1002025-08-31 08:55:00app_open1
1012025-08-31 09:10:00app_open2
1022025-08-31 13:00:00app_open3
1032025-08-31 20:00:00app_open4
1042025-08-31 10:00:00app_open6
1052025-08-31 11:00:00app_open10

Expected output

Single-row, single-column result containing the count of initiators who had > 3 distinct non-test callees across completed video calls they initiated in the time window.

initiators_over_3_callees
1

Hints

  1. Use COUNT(DISTINCT callee_user_id) to protect against duplicate participant rows or multiple joins.
  2. Filter to completed video calls within the explicit timestamp window before counting callees.

Percent of FR DAUs who were on a video call yesterday (interval overlap + dedup joins)

Assume UTC timestamps.

Compute, for the day 2025-08-31 (00:00:00 through 23:59:59 inclusive):

  • DAU_FR: DISTINCT users in France (users.country_code='FR') with users.is_test=false who generated ANY event on 2025-08-31.
  • ONCALL_FR: DISTINCT users in France (users.country_code='FR') with users.is_test=false who participated (initiator OR callee) in ANY completed video call where their participant interval [joined_at, left_at] overlaps the day (i.e., interval overlap between [joined_at, left_at] and [2025-08-31 00:00:00, 2025-08-31 23:59:59]).

Return a single row with:

  • numerator (ONCALL_FR)
  • denominator (DAU_FR)
  • percentage_oncall_fr (numerator/denominator*100) rounded to 2 decimals

Your query must safeguard against double-counting a user who has multiple call_participants rows for the same call (e.g., reconnects).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
country_codeCHAR(2)NOT NULL
created_atDATENOT NULL
is_testBOOLEANNOT NULL

Sample data:

country_codecreated_atis_testuser_id
FR2025-07-01false1
FR2025-08-10false2
US2025-06-20false3
FR2025-08-25false4
DE2025-08-28false5
FR2025-08-01true6
ES2025-07-15false7
IT2025-08-05false8
GB2025-08-12false9
FR2025-08-15false10

Table calls

columntypeconstraints
call_idINTPRIMARY KEY, NOT NULL
initiator_user_idINTNOT NULL
call_typeVARCHAR(10)NOT NULL
statusVARCHAR(10)NOT NULL
started_atTIMESTAMPNOT NULL
ended_atTIMESTAMPNOT NULL

Sample data:

call_idcall_typeended_atinitiator_user_idstarted_atstatus
10video2025-08-30 10:30:0012025-08-30 10:00:00completed
11video2025-08-31 09:05:0012025-08-31 09:00:00failed
12video2025-09-01 12:20:0022025-09-01 12:00:00completed
13audio2025-08-31 13:10:0032025-08-31 13:00:00completed
14video2025-09-01 00:10:0012025-08-31 23:50:00completed
15video2025-08-27 14:10:0012025-08-27 14:00:00completed
16video2025-08-28 15:15:0012025-08-28 15:00:00completed
17video2025-08-29 16:20:0012025-08-29 16:00:00completed
18video2025-08-31 09:45:0022025-08-31 09:30:00completed

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
roleVARCHAR(10)NOT NULL
joined_atTIMESTAMPNOT NULL
left_atTIMESTAMPNOT NULL

Sample data:

call_idjoined_atleft_atroleuser_id
102025-08-30 10:00:002025-08-30 10:30:00initiator1
102025-08-30 10:02:002025-08-30 10:30:00callee2
102025-08-30 10:03:002025-08-30 10:05:00callee2
102025-08-30 10:05:002025-08-30 10:20:00callee4
112025-08-31 09:00:002025-08-31 09:01:00initiator1
112025-08-31 09:00:002025-08-31 09:00:10callee3
122025-09-01 12:00:002025-09-01 12:20:00initiator2
122025-09-01 12:00:052025-09-01 12:20:00callee3
132025-08-31 13:00:002025-08-31 13:10:00initiator3
132025-08-31 13:00:052025-08-31 13:10:00callee5
142025-08-31 23:50:002025-09-01 00:10:00initiator1
142025-08-31 23:51:002025-09-01 00:10:00callee3
152025-08-27 14:00:002025-08-27 14:10:00initiator1
152025-08-27 14:00:102025-08-27 14:10:00callee7
162025-08-28 15:00:002025-08-28 15:15:00initiator1
162025-08-28 15:00:052025-08-28 15:15:00callee8
162025-08-28 15:01:002025-08-28 15:05:00callee6
172025-08-29 16:00:002025-08-29 16:20:00initiator1
172025-08-29 16:00:072025-08-29 16:20:00callee9
182025-08-31 09:30:002025-08-31 09:45:00initiator2
182025-08-31 09:31:002025-08-31 09:40:00callee4
182025-08-31 09:41:002025-08-31 09:45:00callee4

Table events

columntypeconstraints
event_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
event_typeVARCHAR(50)NOT NULL
event_tsTIMESTAMPNOT NULL

Sample data:

event_idevent_tsevent_typeuser_id
1002025-08-31 08:55:00app_open1
1012025-08-31 09:10:00app_open2
1022025-08-31 13:00:00app_open3
1032025-08-31 20:00:00app_open4
1042025-08-31 10:00:00app_open6
1052025-08-31 11:00:00app_open10

Expected output

Single-row result with ONCALL_FR as numerator, DAU_FR as denominator, and the share of DAU_FR who were on a completed video call overlapping 2025-08-31.

denominatornumeratorpercentage_oncall_fr
4375

Hints

  1. Interval overlap for a day can be checked with: joined_at <= day_end AND left_at >= day_start.
  2. Use DISTINCT user_id (or DISTINCT call_id, user_id) to avoid counting reconnects multiple times.