Coding questions · Meta
Write SQL for 7-day WhatsApp call metrics
This question evaluates SQL-based data manipulation and aggregation skills, specifically time-based grouping, filtering by user attributes, deduplication, handling of edge-case durations, and computation of rates and medians from transactional call and event tables.
Today is fixed as 2025-09-01. Using PostgreSQL, write a single query that returns one row per UTC calendar date for the last 7 days inclusive of today (2025-08-26 through 2025-09-01), with columns: date_utc, calls_initiated, completion_rate, drop_rate, median_duration_seconds. Rules: (1) Only count calls where neither caller nor callee is an employee (users.is_employee=false); (2) A call’s date is based on calls.started_at (UTC), even if it ends next day; (3) completion_rate = completed / calls_initiated, drop_rate = dropped / calls_initiated; (4) Median duration is over completed calls only, computed as ended_at − started_at in whole seconds; exclude durations <= 0; (5) Trust calls.outcome over call_events if inconsistent; (6) Do not double-count due to duplicate events; (7) Return exactly 7 rows; (8) Use percentile_cont(0.5) for median. Schema and tiny samples below (UTC timestamps):
users(user_id INT PRIMARY KEY, is_employee BOOLEAN, country TEXT)
calls(call_id INT PRIMARY KEY, caller_id INT, callee_id INT, started_at TIMESTAMP, ended_at TIMESTAMP, outcome TEXT CHECK (outcome IN ('completed','dropped','failed')), network_type TEXT)
call_events(call_id INT, event_time TIMESTAMP, event_type TEXT)
Sample rows:
users
| user_id | is_employee | country |
| 1 | false | US |
| 2 | false | US |
| 3 | true | US |
| 4 | false | IN |
calls
| call_id | caller_id | callee_id | started_at | ended_at | outcome | network_type |
| 10 | 1 | 2 | 2025-08-26 10:00:00 | 2025-08-26 10:05:00 | completed | wifi |
| 11 | 1 | 3 | 2025-08-27 12:00:00 | 2025-08-27 12:01:30 | dropped | lte |
| 12 | 4 | 1 | 2025-09-01 21:59:00 | 2025-09-01 22:10:00 | completed | 3g |
| 13 | 2 | 4 | 2025-08-30 00:00:00 | 2025-08-30 00:00:00 | failed | unknown |
call_events
| call_id | event_time | event_type |
| 10 | 2025-08-26 10:00:00 | start |
| 10 | 2025-08-26 10:05:00 | end |
| 11 | 2025-08-27 12:00:00 | start |
| 11 | 2025-08-27 12:01:30 | drop |
| 11 | 2025-08-27 12:02:00 | reconnect |
| 12 | 2025-09-01 21:59:00 | start |
| 12 | 2025-09-01 22:10:00 | end |
| 13 | 2025-08-30 00:00:00 | start |
Be careful about: excluding employee-linked calls (either side), zero/negative durations, and ensuring dates with zero activity still appear with zeros/nulls.
WhatsApp-style daily call metrics with completion, drop, and median duration
Using PostgreSQL, write a single SQL query that returns one row per UTC calendar date for the 7-day window from 2025-08-26 through 2025-09-01 (inclusive). The output must have the columns:
- date_utc (DATE)
- calls_initiated (INT)
- completion_rate (NUMERIC) = completed_calls / calls_initiated
- drop_rate (NUMERIC) = dropped_calls / calls_initiated
- median_duration_seconds (NUMERIC) = median call duration in whole seconds over completed calls only
Use the schema and sample data below and follow these rules:
- Only count calls where neither the caller nor the callee is an employee (users.is_employee = false for both sides).
- A call’s date is determined by calls.started_at (UTC), even if it ends on the next day.
- calls_initiated counts all non-employee calls (any outcome). completion_rate = completed_calls / calls_initiated. drop_rate = dropped_calls / calls_initiated.
- Median duration is computed per day over completed calls only, as (ended_at − started_at) in whole seconds. Exclude calls whose duration is less than or equal to 0 seconds from the median calculation, but still count them as completed in the rates.
- Trust calls.outcome over call_events if they are inconsistent; use calls.outcome to decide whether a call is completed/dropped/failed.
- call_events may contain multiple or duplicate events per call; they must not cause double-counting of calls.
- Return exactly 7 rows, one for each date from 2025-08-26 to 2025-09-01 inclusive, even if there were no calls on that date. For dates with no calls, calls_initiated should be 0 and the rate and median columns should be NULL.
- Use percentile_cont(0.5) for the median computation.
Write a single SQL query that produces this result.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| is_employee | BOOLEAN | NOT NULL |
| country | VARCHAR(10) |
Sample data:
| country | is_employee | user_id |
|---|---|---|
| US | false | 1 |
| US | false | 2 |
| US | true | 3 |
| IN | false | 4 |
Table calls
| column | type | constraints |
|---|---|---|
| call_id | INT | PRIMARY KEY |
| caller_id | INT | NOT NULL |
| callee_id | INT | NOT NULL |
| started_at | TIMESTAMP | NOT NULL |
| ended_at | TIMESTAMP | NOT NULL |
| outcome | VARCHAR(10) | NOT NULL |
| network_type | VARCHAR(20) |
Sample data:
| call_id | callee_id | caller_id | ended_at | network_type | outcome | started_at |
|---|---|---|---|---|---|---|
| 10 | 2 | 1 | 2025-08-26 10:05:00 | wifi | completed | 2025-08-26 10:00:00 |
| 11 | 3 | 1 | 2025-08-27 12:01:30 | lte | dropped | 2025-08-27 12:00:00 |
| 12 | 1 | 4 | 2025-09-01 22:10:00 | 3g | completed | 2025-09-01 21:59:00 |
| 13 | 4 | 2 | 2025-08-30 00:00:00 | unknown | failed | 2025-08-30 00:00:00 |
| 14 | 1 | 2 | 2025-08-26 22:59:50 | wifi | completed | 2025-08-26 23:00:00 |
Table call_events
| column | type | constraints |
|---|---|---|
| call_id | INT | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
Sample data:
| call_id | event_time | event_type |
|---|---|---|
| 10 | 2025-08-26 10:00:00 | start |
| 10 | 2025-08-26 10:05:00 | end |
| 11 | 2025-08-27 12:00:00 | start |
| 11 | 2025-08-27 12:01:30 | drop |
| 11 | 2025-08-27 12:02:00 | reconnect |
| 12 | 2025-09-01 21:59:00 | start |
| 12 | 2025-09-01 22:10:00 | end |
| 13 | 2025-08-30 00:00:00 | start |
| 14 | 2025-08-26 23:00:00 | start |
| 14 | 2025-08-26 22:59:50 | end |
Expected output
One row per UTC date from 2025-08-26 through 2025-09-01 (inclusive), with call counts and ratios based only on non-employee-to-non-employee calls, and the median duration in seconds over completed calls with positive duration. Dates with no calls appear with zero calls_initiated and NULL rates/median.
| calls_initiated | completion_rate | date_utc | drop_rate | median_duration_seconds |
|---|---|---|---|---|
| 2 | 1 | 2025-08-26 | 0 | 300 |
| 0 | NULL | 2025-08-27 | NULL | NULL |
| 0 | NULL | 2025-08-28 | NULL | NULL |
| 0 | NULL | 2025-08-29 | NULL | NULL |
| 1 | 0 | 2025-08-30 | 0 | NULL |
| 0 | NULL | 2025-08-31 | NULL | NULL |
| 1 | 1 | 2025-09-01 | 0 | 660 |
Hints
- Generate the 7 calendar dates with generate_series and LEFT JOIN your aggregates onto it so that days with no calls still appear.
- Filter out employee-linked calls via joins to users, and use conditional aggregation (FILTER) plus percentile_cont over a per-day grouping to compute the rates and median duration.