First and Last Call On the Same Day solution and explanation
The problem
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.
Approach overview
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.
Solution
# Write your MySQL query statement below
with s as (
select
*
from
Calls
union
all
select
recipient_id,
caller_id,
call_time
from
Calls
),
t as (
select
caller_id user_id,
FIRST_VALUE(recipient_id) over(
partition by DATE_FORMAT(call_time, '%Y-%m-%d'),
caller_id
order by
call_time asc
) first,
FIRST_VALUE(recipient_id) over(
partition by DATE_FORMAT(call_time, '%Y-%m-%d'),
caller_id
order by
call_time desc
) last
from
s
)
select
distinct user_id
from
t
where
first = lastComplexity analysis
| 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 |
Frequently asked questions
Is First and Last Call On the Same Day easy or hard?
First and Last Call On the Same Day is rated Hard because it requires careful SQL reasoning. Handling bidirectional calls, partitioning by user and date, and correctly identifying first and last events makes it more complex than standard aggregation queries.
First and Last Call On the Same Day Python/Java solution
This problem is categorized as a database problem on LeetCode, so the expected solution is written in SQL rather than Python or Java. The logic focuses on query transformations using UNION ALL, window functions, and grouping operations.
How to solve First and Last Call On the Same Day in O(n)?
Pure O(n) is generally not achievable in SQL because ranking or grouping operations require sorting. The closest practical approach uses window functions with ROW_NUMBER(), which performs partition sorting and results in O(n log n) complexity.
What is the best approach for First and Last Call On the Same Day?
The most efficient approach uses SQL window functions. Normalize calls so each participant appears as a user, partition rows by user and date, then rank calls using ROW_NUMBER() ordered by time ascending and descending. If the contact for the first and last ranked rows matches, the user qualifies.
Is First and Last Call On the Same Day asked at Google/Amazon/Meta?
Database query problems involving window functions, ranking, and grouping patterns frequently appear in SQL interview rounds at companies like Amazon, Meta, and Google. Variants of this problem test understanding of event ordering and per-user daily aggregation.
What data structure is used in First and Last Call On the Same Day?
The solution primarily relies on relational database operations such as partitions, sorting, and grouping rather than traditional data structures. Window functions act similarly to ordered grouping structures that track first and last events within partitions.
What is the time complexity of First and Last Call On the Same Day?
The typical SQL solution runs in O(n log n) time due to sorting within window function partitions or during aggregation. Space complexity is O(n) because intermediate normalized call records are stored during query execution.