Coding questions · Meta
Compute callers contacting >3 people last 7 days
This question evaluates a candidate's ability to perform event-level data manipulation in SQL, including time-window filtering, deduplication of callee targets, exclusion of self-calls, and aggregation of distinct callee counts per caller.
Using the schema below, write a single SQL query to return the number of unique callers who started calls with more than 3 distinct other users during 2025-08-26 00:00:00 to 2025-09-01 23:59:59 UTC (inclusive). Count a "started call" only by the caller_id (ignore inbound rings), treat multiple calls to the same callee_id as one, exclude self-calls (caller_id = callee_id), and ignore calls outside the window. Assume UTC timestamps.
Schema:
- calls(call_id INT, caller_id INT, callee_id INT, started_at TIMESTAMP, ended_at TIMESTAMP, media_type TEXT, status TEXT)
Sample rows (subset):
+---------+-----------+----------+---------------------+------------+-----------+
| call_id | caller_id | callee_id| started_at | media_type | status |
+---------+-----------+----------+---------------------+------------+-----------+
| 1001 | 10 | 21 | 2025-08-26 09:01:00 | video | completed |
| 1002 | 10 | 22 | 2025-08-27 11:15:00 | audio | completed |
| 1003 | 10 | 21 | 2025-08-28 12:00:00 | video | missed |
| 1004 | 10 | 23 | 2025-08-29 08:20:00 | video | completed |
| 1005 | 10 | 24 | 2025-08-30 17:45:00 | audio | completed |
| 1006 | 10 | 25 | 2025-09-01 07:30:00 | video | completed |
| 1007 | 11 | 12 | 2025-08-31 10:00:00 | video | completed |
| 1008 | 11 | 12 | 2025-09-01 10:00:00 | video | completed |
| 1009 | 12 | 12 | 2025-08-31 10:05:00 | audio | completed |
| 1010 | 13 | 30 | 2025-08-25 10:05:00 | video | completed |
+---------+-----------+----------+---------------------+------------+-----------+
Return a single integer column named caller_cnt.
Count callers contacting more than 3 distinct users in a time window
Using the calls table defined below, write a single SQL query to return the number of unique callers who started calls with more than 3 distinct other users during the period from 2025-08-26 00:00:00 UTC to 2025-09-01 23:59:59 UTC (inclusive).
Rules:
- Count a "started call" only by the caller_id (ignore inbound rings or other interpretations).
- Treat multiple calls from the same caller_id to the same callee_id as a single contacted user.
- Exclude self-calls where caller_id = callee_id.
- Ignore any calls that started outside the specified time window.
- Assume all timestamps are stored in UTC.
Return a single integer column named caller_cnt.
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 | |
| media_type | VARCHAR(20) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| call_id | callee_id | caller_id | ended_at | media_type | started_at | status |
|---|---|---|---|---|---|---|
| 1001 | 21 | 10 | 2025-08-26 09:15:00 | video | 2025-08-26 09:01:00 | completed |
| 1002 | 22 | 10 | 2025-08-27 11:45:00 | audio | 2025-08-27 11:15:00 | completed |
| 1003 | 21 | 10 | 2025-08-28 12:05:00 | video | 2025-08-28 12:00:00 | missed |
| 1004 | 23 | 10 | 2025-08-29 08:50:00 | video | 2025-08-29 08:20:00 | completed |
| 1005 | 24 | 10 | 2025-08-30 18:10:00 | audio | 2025-08-30 17:45:00 | completed |
| 1006 | 25 | 10 | 2025-09-01 07:55:00 | video | 2025-09-01 07:30:00 | completed |
| 1007 | 12 | 11 | 2025-08-31 10:20:00 | video | 2025-08-31 10:00:00 | completed |
| 1008 | 12 | 11 | 2025-09-01 10:10:00 | video | 2025-09-01 10:00:00 | completed |
| 1009 | 12 | 12 | 2025-08-31 10:06:00 | audio | 2025-08-31 10:05:00 | completed |
| 1010 | 30 | 13 | 2025-08-25 10:25:00 | video | 2025-08-25 10:05:00 | completed |
| 1011 | 40 | 14 | 2025-08-27 09:30:00 | audio | 2025-08-27 09:00:00 | completed |
| 1012 | 40 | 14 | 2025-08-28 09:20:00 | audio | 2025-08-28 09:00:00 | completed |
Expected output
A single-row, single-column result with the count of callers who, within the given time window, started calls to more than 3 distinct other users (excluding self-calls and counting each callee at most once per caller). Based on the sample data, only caller_id = 10 contacted 5 distinct other users (21, 22, 23, 24, 25) in the window, so the count is 1.
| caller_cnt |
|---|
| 1 |
Hints
- First filter calls to the specified time window and exclude self-calls.
- Group by caller_id and use COUNT(DISTINCT callee_id), then count how many callers exceed the threshold.