cswork / libraryOpen practice workspace

Coding questions · Meta

Write SQL to analyze group-call concurrency

This question evaluates advanced SQL competency in temporal interval analysis, concurrency counting, percentile aggregation, and complex user-filtering logic, testing skills in data manipulation, analytic/window functions, and performance-aware querying.

You are given call data and must compute group-call metrics. Schema (timestamps are UTC):

Tables:

  • calls(call_id INT PRIMARY KEY, host_user_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, is_group_enabled BOOLEAN)
  • call_participants(call_id INT, user_id INT, join_ts TIMESTAMP, leave_ts TIMESTAMP NULL)
  • users(user_id INT PRIMARY KEY, email TEXT, country TEXT, is_test BOOLEAN)

Sample rows:

users
user_id | email | country | is_test
10 | a@alpha.com | US | false
11 | b@example.com | US | true
12 | c@alpha.com | US | false
13 | d@beta.com | CA | false
14 | e@alpha.com | US | false
15 | f@alpha.com | US | false

calls
call_id | host_user_id | start_ts | end_ts | is_group_enabled
1 | 10 | 2025-08-31 09:00:00 | 2025-08-31 09:30:00 | true
2 | 11 | 2025-09-01 10:00:00 | 2025-09-01 10:45:00 | true
3 | 10 | 2025-09-01 11:00:00 | 2025-09-01 11:07:00 | true

call_participants
call_id | user_id | join_ts | leave_ts
1 | 10 | 2025-08-31 09:00:00 | 2025-08-31 09:30:00
1 | 12 | 2025-08-31 09:02:00 | 2025-08-31 09:15:00
1 | 13 | 2025-08-31 09:04:00 | 2025-08-31 09:20:00
1 | 14 | 2025-08-31 09:05:00 | NULL
2 | 11 | 2025-09-01 10:00:00 | 2025-09-01 10:45:00
2 | 12 | 2025-09-01 10:02:00 | 2025-09-01 10:10:00
2 | 13 | 2025-09-01 10:02:00 | 2025-09-01 10:40:00
2 | 14 | 2025-09-01 10:15:00 | 2025-09-01 10:30:00
2 | 15 | 2025-09-01 10:33:00 | 2025-09-01 10:42:00
3 | 10 | 2025-09-01 11:00:00 | 2025-09-01 11:07:00
3 | 12 | 2025-09-01 11:06:00 | 2025-09-01 11:07:00

Tasks (write SQL; one query if possible, CTEs allowed):

  1. Define a call’s peak concurrent participants as the maximum number of overlapping participant intervals within [start_ts, end_ts], where each participant interval is [join_ts, COALESCE(leave_ts, end_ts)]. Exclude test users (users.is_test = true) and any user whose email domain is 'example.com' from both host and participant counts. A call is a "group call" if its peak concurrency ≥ 3.
  2. For each calendar day in 2025-08-26 through 2025-09-01 (inclusive; treat "today" as 2025-09-01), return: day, total calls started that day, number of group calls started that day, and the 90th percentile (P90) of peak concurrency among calls started that day. Only include calls where is_group_enabled = true.
  3. Additionally, return the top 3 calls (by peak concurrency) that started on 2025-09-01 with: call_id, host_user_id, start_ts, peak_concurrency, and the first timestamp when concurrency first reached 3 within the first 10 minutes of the call (NULL if never reached). Constraints/edge cases to handle explicitly in SQL: overlapping intervals, NULL leave_ts, hosts or participants filtered by test/email-domain rules, and ties in top-3 broken by earlier start_ts then smaller call_id. Explain your approach to computing overlaps (e.g., +1/-1 event expansion with running SUM) and to computing P90 in ANSI SQL.

Compute peak concurrent participants per call

You are given three tables with call and participant data. For each call where is_group_enabled = TRUE, compute the call's peak concurrent participants and determine whether it is a group call.

Definitions and rules:

  • A participant's effective interval for a call is [join_ts, COALESCE(leave_ts, end_ts)], clamped to the call's [start_ts, end_ts] window.
  • A call's peak concurrent participants is the maximum number of overlapping participant intervals at any instant within the call's [start_ts, end_ts].
  • Exclude all test users (users.is_test = TRUE) and any user whose email domain is 'example.com' from the participant counts. This exclusion applies to both hosts and non-host participants, but calls themselves are not filtered out by host type.
  • A call is a "group call" if its peak concurrency is greater than or equal to 3.

Task: Write a single SQL query (you may use CTEs) that returns one row per call with is_group_enabled = TRUE, with the columns:

  • call_id
  • peak_concurrency (an integer)
  • is_group_call (BOOLEAN, TRUE if peak_concurrency >= 3, otherwise FALSE)

Explain your approach to computing overlapping intervals in SQL (for example, by expanding each interval into +1 / -1 events and using a running SUM).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
emailVARCHAR(255)NOT NULL
countryVARCHAR(2)NOT NULL
is_testBOOLEANNOT NULL

Sample data:

countryemailis_testuser_id
USa@alpha.comfalse10
USb@example.comtrue11
USc@alpha.comfalse12
CAd@beta.comfalse13
USe@alpha.comfalse14
USf@alpha.comfalse15

Table calls

columntypeconstraints
call_idINTPRIMARY KEY, NOT NULL
host_user_idINTNOT NULL
start_tsTIMESTAMPNOT NULL
end_tsTIMESTAMPNOT NULL
is_group_enabledBOOLEANNOT NULL

Sample data:

call_idend_tshost_user_idis_group_enabledstart_ts
12025-08-31 09:30:0010true2025-08-31 09:00:00
22025-09-01 10:45:0011true2025-09-01 10:00:00
32025-09-01 11:07:0010true2025-09-01 11:00:00

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
join_tsTIMESTAMPNOT NULL
leave_tsTIMESTAMP

Sample data:

call_idjoin_tsleave_tsuser_id
12025-08-31 09:00:002025-08-31 09:30:0010
12025-08-31 09:02:002025-08-31 09:15:0012
12025-08-31 09:04:002025-08-31 09:20:0013
12025-08-31 09:05:00NULL14
22025-09-01 10:00:002025-09-01 10:45:0011
22025-09-01 10:02:002025-09-01 10:10:0012
22025-09-01 10:02:002025-09-01 10:40:0013
22025-09-01 10:15:002025-09-01 10:30:0014
22025-09-01 10:33:002025-09-01 10:42:0015
32025-09-01 11:00:002025-09-01 11:07:0010
32025-09-01 11:06:002025-09-01 11:07:0012

Expected output

One row per call with is_group_enabled = TRUE, showing the call's peak concurrent participants after filtering out test/example.com users, and whether it qualifies as a group call (peak_concurrency >= 3).

call_idis_group_callpeak_concurrency
1true4
2false2
3false2

Hints

  1. First filter out test users and example.com emails by joining call_participants to users.
  2. To get concurrency, turn each interval into +1 (join) and -1 (leave) events and use a running SUM window function per call.

Daily group-call metrics and P90 peak concurrency

Using the same tables and definitions as in Question 1, you now need a daily summary of call activity.

Definitions and rules:

  • Treat "today" as 2025-09-01.
  • Consider only calls where is_group_enabled = TRUE.
  • A call's peak concurrent participants and group-call definition are as in Question 1 (using filtered participants and overlap logic).

Task: For each calendar day from 2025-08-26 through 2025-09-01 (inclusive), return one row with:

  • day (DATE)
  • total_calls_started: number of calls with start_ts on that day
  • group_calls_started: number of those calls that are group calls (peak_concurrency >= 3)
  • p90_peak_concurrency: the 90th percentile (P90) of peak_concurrency among calls started that day

Include days even if there are zero calls (in that case, total_calls_started and group_calls_started should be 0, and p90_peak_concurrency should be NULL). Use only calls where is_group_enabled = TRUE.

Explain briefly how you compute the P90 in ANSI SQL (for example, by using an ordered-set aggregate such as PERCENTILE_CONT or an equivalent window-function approach).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
emailVARCHAR(255)NOT NULL
countryVARCHAR(2)NOT NULL
is_testBOOLEANNOT NULL

Sample data:

countryemailis_testuser_id
USa@alpha.comfalse10
USb@example.comtrue11
USc@alpha.comfalse12
CAd@beta.comfalse13
USe@alpha.comfalse14
USf@alpha.comfalse15

Table calls

columntypeconstraints
call_idINTPRIMARY KEY, NOT NULL
host_user_idINTNOT NULL
start_tsTIMESTAMPNOT NULL
end_tsTIMESTAMPNOT NULL
is_group_enabledBOOLEANNOT NULL

Sample data:

call_idend_tshost_user_idis_group_enabledstart_ts
12025-08-31 09:30:0010true2025-08-31 09:00:00
22025-09-01 10:45:0011true2025-09-01 10:00:00
32025-09-01 11:07:0010true2025-09-01 11:00:00

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
join_tsTIMESTAMPNOT NULL
leave_tsTIMESTAMP

Sample data:

call_idjoin_tsleave_tsuser_id
12025-08-31 09:00:002025-08-31 09:30:0010
12025-08-31 09:02:002025-08-31 09:15:0012
12025-08-31 09:04:002025-08-31 09:20:0013
12025-08-31 09:05:00NULL14
22025-09-01 10:00:002025-09-01 10:45:0011
22025-09-01 10:02:002025-09-01 10:10:0012
22025-09-01 10:02:002025-09-01 10:40:0013
22025-09-01 10:15:002025-09-01 10:30:0014
22025-09-01 10:33:002025-09-01 10:42:0015
32025-09-01 11:00:002025-09-01 11:07:0010
32025-09-01 11:06:002025-09-01 11:07:0012

Expected output

One row per day from 2025-08-26 to 2025-09-01 inclusive. For each day, show total calls started, how many of those are group calls (peak_concurrency >= 3), and the 90th percentile of peak_concurrency for that day's calls. Days with no calls have 0 counts and NULL P90.

daygroup_calls_startedp90_peak_concurrencytotal_calls_started
2025-08-260NULL0
2025-08-270NULL0
2025-08-280NULL0
2025-08-290NULL0
2025-08-300NULL0
2025-08-31141
2025-09-01022

Hints

  1. Put RECURSIVE immediately after WITH when any CTE in the list is recursive.
  2. Create a date spine so days without calls return zero counts and NULL P90.

Top calls and first time reaching 3-way concurrency

Using the same tables and concurrency definition as before, analyze calls that started on 2025-09-01.

Definitions and rules:

  • Consider only calls where is_group_enabled = TRUE.
  • Peak concurrent participants and participant filtering (test users and 'example.com' domain) are as in Question 1.
  • For each call, define first_reach_3_ts as the earliest timestamp at which the call's concurrency first reached 3 participants, but only if this happens within the first 10 minutes after start_ts. If concurrency never reaches 3 within the first 10 minutes, this value should be NULL.

Task: Write a SQL query that returns the top 3 calls (by peak_concurrency) that started on 2025-09-01, with the columns:

  • call_id
  • host_user_id
  • start_ts
  • peak_concurrency
  • first_reach_3_ts (as defined above)

If fewer than 3 calls started on that date, return all of them. Order the result by:

  1. peak_concurrency descending, 2) start_ts ascending, 3) call_id ascending (as a final tie-breaker).

Use CTEs if needed, and explain in comments how you compute the time when concurrency first reaches 3.

Render start_ts and first_reach_3_ts as YYYY-MM-DD HH24:MI:SS; first_reach_3_ts remains NULL if concurrency never reaches 3 in the first 10 minutes.

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
emailVARCHAR(255)NOT NULL
countryVARCHAR(2)NOT NULL
is_testBOOLEANNOT NULL

Sample data:

countryemailis_testuser_id
USa@alpha.comfalse10
USb@example.comtrue11
USc@alpha.comfalse12
CAd@beta.comfalse13
USe@alpha.comfalse14
USf@alpha.comfalse15

Table calls

columntypeconstraints
call_idINTPRIMARY KEY, NOT NULL
host_user_idINTNOT NULL
start_tsTIMESTAMPNOT NULL
end_tsTIMESTAMPNOT NULL
is_group_enabledBOOLEANNOT NULL

Sample data:

call_idend_tshost_user_idis_group_enabledstart_ts
12025-08-31 09:30:0010true2025-08-31 09:00:00
22025-09-01 10:45:0011true2025-09-01 10:00:00
32025-09-01 11:07:0010true2025-09-01 11:00:00

Table call_participants

columntypeconstraints
call_idINTNOT NULL
user_idINTNOT NULL
join_tsTIMESTAMPNOT NULL
leave_tsTIMESTAMP

Sample data:

call_idjoin_tsleave_tsuser_id
12025-08-31 09:00:002025-08-31 09:30:0010
12025-08-31 09:02:002025-08-31 09:15:0012
12025-08-31 09:04:002025-08-31 09:20:0013
12025-08-31 09:05:00NULL14
22025-09-01 10:00:002025-09-01 10:45:0011
22025-09-01 10:02:002025-09-01 10:10:0012
22025-09-01 10:02:002025-09-01 10:40:0013
22025-09-01 10:15:002025-09-01 10:30:0014
22025-09-01 10:33:002025-09-01 10:42:0015
32025-09-01 11:00:002025-09-01 11:07:0010
32025-09-01 11:06:002025-09-01 11:07:0012

Expected output

Top calls (up to 3) that started on 2025-09-01, ordered by peak_concurrency descending, then start_ts ascending, then call_id ascending. For each call, show host_user_id, start_ts, peak_concurrency, and the first timestamp within the first 10 minutes when concurrency reached 3 participants (NULL if never). In the sample data, no call on 2025-09-01 reaches concurrency 3, so first_reach_3_ts is NULL for both calls.

call_idfirst_reach_3_tshost_user_idpeak_concurrencystart_ts
2NULL1122025-09-01 10:00:00
3NULL1022025-09-01 11:00:00

Hints

  1. Reuse the event-expansion and running SUM approach to get a full concurrency timeline per call.
  2. Compute the MIN(event_ts) where concurrency >= 3 and event_ts <= start_ts + INTERVAL '10 minutes' for each call, then join this to per-call peak_concurrency and apply the requested ordering and TOP 3.
  3. Use TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') to format timestamp outputs in PostgreSQL.