First and Last Call On the Same Day

First and Last Call On the Same Day

1. What is this problem about?

The First and Last Call On the Same Day interview question is a SQL task designed to find specific interactions. You are given a table of calls between users. For each user and each day they made at least one call, you need to identify their very first call and their very last call of that day. If the person they called first is the same person they called last, that user is included in the final report.

2. Why is this asked in interviews?

Amazon asks the First and Last Call coding problem to evaluate your mastery of Window Functions and complex filtering. It tests your ability to order data within partitions (user and day) and compare attributes across different rows. It evaluation your Database interview pattern skills for behavioral logging analysis.

3. Algorithmic pattern used

This problem follows the Ranking and Comparison pattern in SQL.

1. Normalize Calls: Since a call between A and B is an event for both A and B, create a temporary table/CTE where each call is listed twice: once from A's perspective and once from B's.
2. Window Functions: Use FIRST_VALUE(receiver_id) OVER(PARTITION BY caller_id, date ORDER BY timestamp) and LAST_VALUE(...) OVER(...) to identify the first and last person contacted. (Note: use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for the last value).
3. Distinct Filter: Select rows where first_receiver == last_receiver and return the unique user IDs.

4. Example explanation

User 1 calls User 2 at 10:00 AM. User 1 calls User 3 at 12:00 PM. User 1 calls User 2 at 5:00 PM.

• First call of day: User 2.
• Last call of day: User 2.
• Same person? Yes. User 1 is included.

5. Common mistakes candidates make

• One-way calls: Only considering the person who initiated the call. A call is a "contact" for both parties.
• Last Value error: Forgetting that LAST_VALUE in SQL defaults to the range from start to current row. You must define the full window range explicitly.
• Tie-breaking: Not handling cases where two calls happen at the exact same timestamp (though usually timestamps are unique).

6. Interview preparation tip

Practice using FIRST_VALUE and LAST_VALUE. These are specialized window functions that are often cleaner than using RANK() = 1 or ROW_NUMBER() when you only need to compare values from the boundaries of a sorted set.