Count Users Who Called More Than Three Different People in the Last 7 Days
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
A product logs every one-to-one video call in a single table. Write a query that returns how many users started a call with more than three different people in the last 7 days.
### Input Table(s)
**`video_calls`**: one row per call. Every call is between exactly two people.
| Column | Type | Meaning |
| --- | --- | --- |
| `caller` | BIGINT | ID of the user who made the call |
| `recipient` | BIGINT | ID of the user who was called |
| `ds` | TEXT | Date of the call, formatted `YYYY-MM-DD` |
| `call_id` | BIGINT | ID of the call; primary key, unique per call |
| `duration` | DOUBLE PRECISION | Length of the call in seconds |
### Output Contract
- Write one read-only PostgreSQL query: a single `SELECT`, which may use CTEs.
- Return exactly one row with one column:
| Column | Type | Meaning |
| --- | --- | --- |
| `num_users` | BIGINT | Number of distinct users who, as the caller, called more than three different recipients during the window |
- The result always has exactly one row, so it needs no `ORDER BY`.
### Example
**`video_calls`**
| caller | recipient | ds | call_id | duration |
| --- | --- | --- | --- | --- |
| 101 | 201 | 2019-06-25 | 1 | 120.0 |
| 101 | 202 | 2019-06-27 | 2 | 45.5 |
| 101 | 203 | 2019-06-30 | 3 | 600.0 |
| 101 | 204 | 2019-07-01 | 4 | 12.0 |
| 102 | 201 | 2019-06-26 | 5 | 300.0 |
| 102 | 201 | 2019-06-28 | 6 | 310.0 |
| 102 | 202 | 2019-06-29 | 7 | 50.0 |
| 102 | 203 | 2019-06-29 | 8 | 75.0 |
| 102 | 204 | 2019-06-24 | 9 | 20.0 |
| 103 | 101 | 2019-06-26 | 10 | 30.0 |
| 103 | 102 | 2019-06-26 | 11 | 31.0 |
| 103 | 201 | 2019-06-27 | 12 | 32.0 |
| 103 | 202 | 2019-07-02 | 13 | 33.0 |
| 202 | 105 | 2019-06-26 | 14 | 90.0 |
| 202 | 106 | 2019-06-30 | 15 | 95.0 |
**Expected result**
| num_users |
| --- |
| 1 |
- User 101 called 201, 202, 203 and 204 inside the window, four different people, so 101 is counted. Its calls on 2019-06-25 and 2019-07-01 fall on the first and last days of the window.
- User 102 made four calls in the window but to only three different people (201 twice, 202 and 203). The call to 204 on 2019-06-24 is before the window.
- User 103 called 101, 102 and 201 in the window. The call to 202 on 2019-07-02 is dated today and is excluded, leaving three.
- User 202 started calls to only two people, 105 and 106. The calls 202 received from 101 and 102 do not count, because 202 did not start them.
### Constraints and Clarifications
- **Reference date.** Treat today as 2019-07-02. This fixed date replaces the current date so the answer is deterministic; do not use `CURRENT_DATE` or `NOW()`. The last 7 days are the seven full days before today, 2019-06-25 through 2019-07-01 inclusive. Rows dated today or later, or before 2019-06-25, are ignored. The table may hold any range of dates.
- **Started a call** means the user is the `caller`. Calls a user received as `recipient` do not count toward that user.
- **Different people** means distinct `recipient` IDs across the whole 7-day window, not per day. Several calls to the same recipient count once.
- **More than three** is strict: a caller needs at least four different recipients in the window.
- Every row counts as a call whatever its `duration`; `duration` plays no part in the answer.
- `caller`, `recipient`, `ds` and `call_id` are never NULL, every `ds` is a valid date in `YYYY-MM-DD` form, and `caller` never equals `recipient`.
- If no user qualifies, return one row with `num_users` equal to 0.
```hint Repeat calls
A user who calls the same friend every day of the week has still called only one person.
```
Overview: A SQL question on a log of one-to-one video calls: count the users who started calls with more than three different people during the last seven days. It tests date-window filtering, distinct counting per caller, a strict threshold and returning a single aggregate row.