Daily First-Last Call Counterpart Match

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

• caller_id: user initiating the call
• recipient_id: user receiving the call
• call_time: timestamp of the call event in YYYY-MM-DD HH:MM:SS format

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:

• Every call contributes to both participants:
  • caller_id sees recipient_id as counterpart
  • recipient_id sees caller_id as counterpart
• First/last are computed per (user_id, calendar day) by call_time.

Deterministic tie-breaking:

• If multiple calls share the earliest timestamp for a user/day, pick the smallest counterpart_id as the first counterpart.
• If multiple calls share the latest timestamp for a user/day, pick the largest counterpart_id as the last counterpart.

Qualification rule:

• A user is included if they satisfy the condition on at least one day.
• Return each qualifying user only once.

Output requirements:

• Return exactly one column:
  • user_id
• Sort by user_id in ascending order.

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)

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}
]

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}
]

calls:
| caller_id | recipient_id | call_time |
|-----------|--------------|-----------|
| (no rows) |

[]