Loading problem...
A telecom observability platform stores every phone interaction as a directed call event. Product analysts want to identify users whose communication day starts and ends with the same counterpart at least once.
Table: calls
Task: Return user IDs such that there exists at least one calendar day where that user's first and last call counterpart are the same person.
How calls are interpreted:
Deterministic tie-breaking:
Qualification rule:
Output requirements:
Supported submission environments:
calls:
| caller_id | recipient_id | call_time |
|-----------|--------------|----------------------|
| 8 | 4 | 2021-08-24 17:46:07 |
| 4 | 8 | 2021-08-24 19:57:13 |
| 5 | 1 | 2021-08-11 05:28:44 |
| 8 | 3 | 2021-08-17 04:04:15 |
| 11 | 3 | 2021-08-17 13:07:00 |
| 8 | 11 | 2021-08-17 22:22:22 |[
{"user_id":1},
{"user_id":4},
{"user_id":5},
{"user_id":8}
]User 8 qualifies on 2021-08-24 because both first and last counterparts are user 4. Users 1 and 5 each have one call on 2021-08-11, so their first and last counterpart are also the same by definition.
calls:
| caller_id | recipient_id | call_time |
|-----------|--------------|----------------------|
| 100 | 200 | 2023-05-10 08:00:00 |
| 300 | 100 | 2023-05-10 08:00:00 |
| 100 | 300 | 2023-05-10 21:00:00 |
| 200 | 100 | 2023-05-10 21:00:00 |[
{"user_id":200},
{"user_id":300}
]For user 100, earliest-time tie picks counterpart 200 and latest-time tie picks counterpart 300, so user 100 does not qualify. Users 200 and 300 each interact only with user 100 on that day, so both qualify.
calls:
| caller_id | recipient_id | call_time |
|-----------|--------------|-----------|
| (no rows) |[]With no calls, no users can satisfy the condition.
Constraints