Write SQL for 7-day WhatsApp call metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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:
1. Only count calls where **neither** the caller nor the callee is an employee (users.is_employee = false for both sides).
2. A call’s date is determined by calls.started_at (UTC), even if it ends on the next day.
3. calls_initiated counts all non-employee calls (any outcome). completion_rate = completed_calls / calls_initiated. drop_rate = dropped_calls / calls_initiated.
4. 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.
5. Trust calls.outcome over call_events if they are inconsistent; use calls.outcome to decide whether a call is completed/dropped/failed.
6. call_events may contain multiple or duplicate events per call; they must not cause double-counting of calls.
7. 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.
8. Use percentile_cont(0.5) for the median computation.
Write a single SQL query that produces this result.
Tables
users(user_id INT, is_employee BOOLEAN, country VARCHAR(10))
calls(call_id INT, caller_id INT, callee_id INT, started_at TIMESTAMP, ended_at TIMESTAMP, outcome VARCHAR(10), network_type VARCHAR(20))
call_events(call_id INT, event_time TIMESTAMP, event_type VARCHAR(20))
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.