First and Last Call On the Same Day - Practice Coding Problems

First and Last Call On the Same Day - Problem

Database Hard

Phone Call Pattern Analysis

You're given a Calls table that tracks phone conversations between users. Your task is to find users whose first call and last call on any given day were with the same person.

The table structure:

Column Name	Type
caller_id	int
recipient_id	int
call_time	datetime

Key Rules:

Calls count regardless of whether the user is caller or recipient
We're looking for patterns within the same day
A user must have made at least 2 calls in a day to qualify
Return user IDs who show this pattern on any day

Example: If User 1 calls User 2 at 9 AM and User 2 calls User 1 at 11 PM on the same day, and these are User 1's first and last calls that day, then User 1 should be in the result.

Input & Output

example_1.sql — Basic Pattern Match

$ Input: Calls table:\n| caller_id | recipient_id | call_time |\n|-----------|--------------|---------------------|\n| 1 | 2 | 2020-07-29 09:00:00 |\n| 2 | 1 | 2020-07-29 17:00:00 |\n| 1 | 3 | 2020-07-29 12:00:00 |

› Output: | user_id |\n|---------|\n| 1 |

💡 Note: User 1's calls on 2020-07-29: First call with User 2 (09:00), call with User 3 (12:00), last call with User 2 (17:00 as recipient). Since first and last calls were with User 2, User 1 is included in result.

example_2.sql — Multiple Users Pattern

$ Input: Calls table:\n| caller_id | recipient_id | call_time |\n|-----------|--------------|---------------------|\n| 1 | 2 | 2020-07-29 09:00:00 |\n| 1 | 3 | 2020-07-29 17:00:00 |\n| 2 | 3 | 2020-07-29 11:00:00 |\n| 3 | 2 | 2020-07-29 18:00:00 |

› Output: | user_id |\n|---------|\n| 2 |

💡 Note: User 2's calls on 2020-07-29: First call with User 1 (09:00 as recipient), last call with User 3 (18:00 as recipient). Different partners, so User 2 is NOT included. Only users with matching first/last call partners qualify.

example_3.sql — Single Call Edge Case

$ Input: Calls table:\n| caller_id | recipient_id | call_time |\n|-----------|--------------|---------------------|\n| 1 | 2 | 2020-07-29 09:00:00 |\n| 3 | 4 | 2020-07-29 10:00:00 |

› Output: | user_id |\n|---------|

💡 Note: Each user made only one call that day. Since we need at least 2 calls to have distinct 'first' and 'last' calls, no users qualify for the result.

Constraints

1 ≤ calls.length ≤ 10⁵
1 ≤ caller_id, recipient_id ≤ 10³
caller_id != recipient_id
call_time is a valid datetime in format 'YYYY-MM-DD HH:MM:SS'

Visualization

Tap to expand

Understanding the Visualization

Normalize Call Data

Transform caller/recipient data into user perspective - every call appears twice, once for each participant

Rank Daily Calls

For each user-date, rank calls by time: 1st, 2nd, 3rd... and also rank in reverse: last, 2nd-to-last...

Extract First & Last

Identify who was called first (rank 1 ascending) and last (rank 1 descending) each day

Pattern Matching

Find users where first_call_partner = last_call_partner for any day with 2+ calls

Key Takeaway

🎯 Key Insight: Window functions provide an elegant solution by ranking calls within user-date partitions, allowing us to efficiently identify first and last call patterns in a single database scan.

Asked in

f Meta 45 a Amazon 38 G Google 32 ⊞ Microsoft 28

The optimal approach uses window functions to efficiently identify first and last calls for each user-date combination in a single pass. Key steps: 1) Use UNION ALL to get both caller/recipient perspectives, 2) Apply ROW_NUMBER() with ORDER BY call_time ASC/DESC to rank calls, 3) Extract first/last call partners using CASE statements, 4) Compare partners and filter for users with multiple daily calls. Time complexity: O(n log n) due to sorting in window functions.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n³)	O(n²)	Use multiple nested queries to find first and last calls separately
Window Functions Approach (Optimal)	O(n log n)	O(n)	Use window functions to identify first and last calls efficiently in one pass

Nested Subqueries Approach — Algorithm Steps

Create subquery to get all calls with user and other_user normalized
Create subquery to find first call time for each user-date
Create subquery to find last call time for each user-date
Join first and last call data to compare the other_user values
Filter for cases where first and last calls are with same person

Visualization

Tap to expand

Step-by-Step Walkthrough

First Scan

Create user_calls view by scanning Calls table twice (UNION ALL)

Second Scan

Scan user_calls to find first call per user-date

Third Scan

Scan user_calls again to find last call per user-date

Join Results

Join first_calls and last_calls tables

Filter

Apply WHERE conditions to find matching patterns

Code -

solution.c — C

#include <stdio.h>

int main() {
    char* sqlQuery = 
    "WITH user_calls AS (\n"
    "    SELECT caller_id as user_id, recipient_id as other_user, call_time,\n"
    "           DATE(call_time) as call_date\n"
    "    FROM Calls\n"
    "    UNION ALL\n"
    "    SELECT recipient_id as user_id, caller_id as other_user, call_time,\n"
    "           DATE(call_time) as call_date\n"
    "    FROM Calls\n"
    "),\n"
    "first_calls AS (\n"
    "    SELECT user_id, call_date, other_user,\n"
    "           ROW_NUMBER() OVER (PARTITION BY user_id, call_date ORDER BY call_time) as rn\n"
    "    FROM user_calls\n"
    "),\n"
    "last_calls AS (\n"
    "    SELECT user_id, call_date, other_user,\n"
    "           ROW_NUMBER() OVER (PARTITION BY user_id, call_date ORDER BY call_time DESC) as rn\n"
    "    FROM user_calls\n"
    ")\n"
    "SELECT DISTINCT f.user_id\n"
    "FROM first_calls f\n"
    "JOIN last_calls l ON f.user_id = l.user_id AND f.call_date = l.call_date\n"
    "WHERE f.rn = 1 AND l.rn = 1 AND f.other_user = l.other_user\n"
    "  AND f.user_id IN (\n"
    "    SELECT user_id FROM user_calls\n"
    "    GROUP BY user_id, call_date\n"
    "    HAVING COUNT(*) > 1\n"
    "  )";
    
    printf("%s\n", sqlQuery);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

Multiple nested scans of the calls table with joins

⚠ Quadratic Growth

Space Complexity

O(n²)

Intermediate result sets for first/last call subqueries

⚠ Quadratic Space

42.0K Views

High Frequency

~25 min Avg. Time

1.4K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler