Compute callers contacting >3 people last 7 days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
Read the full Meta Data Scientist interview experience this question came from
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.
Tables
calls(call_id INT, caller_id INT, callee_id INT, started_at TIMESTAMP, ended_at TIMESTAMP, media_type VARCHAR(20), status VARCHAR(20))
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.