First and Last Call On the Same Day - Problem

Given a table of phone calls, find users whose first and last calls on any day were with the same person.

The Calls table contains:

  • caller_id: ID of the person making the call
  • recipient_id: ID of the person receiving the call
  • call_time: Timestamp of the call

A call involves both participants equally - whether someone is the caller or recipient, it counts as their call with that person.

Return the IDs of users whose first and last calls on the same day were with the same person.

Table Schema

Calls
Column Name Type Description
caller_id PK int ID of the person making the call
recipient_id PK int ID of the person receiving the call
call_time PK datetime Timestamp when the call occurred
Primary Key: (caller_id, recipient_id, call_time)
Note: Each row represents a phone call between two users at a specific time

Input & Output

Example 1 — Basic Pattern Matching
Input Table:
caller_id recipient_id call_time
1 2 2024-07-09 09:00:00
1 3 2024-07-09 17:00:00
1 2 2024-07-09 18:00:00
2 1 2024-07-10 09:00:00
3 1 2024-07-10 18:00:00
Output:
user_id
1
2
3
💡 Note:

User 1 has calls on 2024-07-09: first call with user 2 at 09:00, middle call with user 3 at 17:00, and last call with user 2 at 18:00. Since the first and last calls were both with user 2, user 1 is included in the result.
Example 2 — No Matching Pattern
Input Table:
caller_id recipient_id call_time
1 2 2024-07-09 09:00:00
1 3 2024-07-09 18:00:00
2 4 2024-07-09 10:00:00
Output:
user_id
3
4
💡 Note:

User 1's first call on 2024-07-09 was with user 2, but the last call was with user 3. No user has matching first and last call partners on any day, so the result is empty.
Example 3 — Single Call Per Day
Input Table:
caller_id recipient_id call_time
1 2 2024-07-09 09:00:00
3 4 2024-07-10 10:00:00
Output:
user_id
1
2
3
4
💡 Note:

When a user has only one call in a day, that call is both their first and last call. Since it's with the same person (trivially), all users are included in the result.

Constraints

  • 1 ≤ caller_id, recipient_id ≤ 1000
  • call_time is a valid datetime
  • No duplicate calls (same caller, recipient, and time)

Visualization

Tap to expand
First and Last Call On the Same Day INPUT caller recipient call_time 1 2 2021-01-01 09:00 1 3 2021-01-01 12:00 1 2 2021-01-01 21:00 2 3 2021-01-01 10:00 3 2 2021-01-01 22:00 Call Network U1 U2 U3 ALGORITHM STEPS 1 Normalize Calls Union caller/recipient as user + other_person pairs 2 Extract Date Group by user and date from call_time 3 Find First/Last Call MIN/MAX call_time per user per day 4 Match Same Person WHERE first_person = last_person user date first last OK? 1 01-01 U2 U2 OK 2 01-01 U1 U3 -- 3 01-01 U1 U2 -- 2 01-01 U1 U1 OK 3 01-01 U2 U2 OK FINAL RESULT Users with same first/last contact: User 1 First: U2, Last: U2 User 2 First: U1, Last: U1 User 3 First: U2, Last: U2 Output: user_id: [1, 2, 3] 3 Users Found Key Insight: 1. Treat both caller and recipient as "users" by creating a UNION of normalized records. 2. Use window functions (FIRST_VALUE, LAST_VALUE) partitioned by user and date to find first/last contacts. 3. Filter where first_contact_person = last_contact_person for each user-day combination. TutorialsPoint - First and Last Call On the Same Day | Optimal Solution

Related Problems

Asked in
Facebook 12 Microsoft 8
23.4K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen