Watch the video solution for First and Last Call On the Same Day, a hard level problem involving Database. This walkthrough by Everyday Data Science has 918 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Calls
+--------------+----------+ | Column Name | Type | +--------------+----------+ | caller_id | int | | recipient_id | int | | call_time | datetime | +--------------+----------+ (caller_id, recipient_id, call_time) is the primary key (combination of columns with unique values) for this table. Each row contains information about the time of a phone call between caller_id and recipient_id.
Write a solution to report the IDs of the users whose first and last calls on any day were with the same person. Calls are counted regardless of being the caller or the recipient.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Calls table: +-----------+--------------+---------------------+ | 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 | +-----------+--------------+---------------------+ Output: +---------+ | user_id | +---------+ | 1 | | 4 | | 5 | | 8 | +---------+ Explanation: On 2021-08-24, the first and last call of this day for user 8 was with user 4. User 8 should be included in the answer. Similarly, user 4 on 2021-08-24 had their first and last call with user 8. User 4 should be included in the answer. On 2021-08-11, user 1 and 5 had a call. This call was the only call for both of them on this day. Since this call is the first and last call of the day for both of them, they should both be included in the answer.
Problem Overview: The Calls table records phone calls with caller_id, recipient_id, and call_time. For every user and each day, determine whether their first call and last call of that day were made with the same person. If both calls involve the same counterpart, that user should appear in the result.
The challenge is that a user can appear as either the caller or the recipient. The solution must normalize both directions so every call is viewed from the perspective of each participant.
Approach 1: Window Functions with Normalized Call Records (O(n log n) time, O(n) space)
Create a normalized dataset where each call appears twice: once from the caller's perspective and once from the recipient's perspective. This can be done using UNION ALL to produce columns like user_id, contact_id, and call_time. Extract the date using DATE(call_time). Then apply SQL window functions such as ROW_NUMBER() ordered by call_time ascending and descending within each (user_id, date) partition.
The row with rank 1 in ascending order represents the first call of the day. The row with rank 1 in descending order represents the last call. If the contact_id for both rows is the same, that user had the same person for their first and last interaction that day. This approach relies on SQL window functions for efficient per‑group ranking and works cleanly in MySQL 8+.
Approach 2: Aggregation with MIN/MAX Call Times (O(n log n) time, O(n) space)
Another option is to first normalize calls using UNION ALL so each user appears as user_id. Then compute the earliest and latest timestamps for every (user_id, date) using MIN(call_time) and MAX(call_time). After that, join these timestamps back to the normalized dataset to retrieve the corresponding contact_id values.
If the contact linked to the minimum timestamp matches the contact linked to the maximum timestamp, the user satisfies the condition. This solution uses classic SQL aggregation and joins instead of window ranking. It’s slightly more verbose but still performs well when indexed on call_time.
Recommended for interviews: The window function solution is typically expected. It shows strong knowledge of database querying patterns such as partitioning, ranking, and handling bidirectional relationships. The aggregation approach demonstrates the same logic using traditional SQL primitives, but the window method is shorter and easier to reason about.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Window Functions with Ranking | O(n log n) | O(n) | Best general solution when window functions are available (MySQL 8+, PostgreSQL, SQL Server) |
| Aggregation with MIN/MAX and Join | O(n log n) | O(n) | Useful when solving the problem with classic SQL aggregation instead of window functions |
