Get the Second Most Recent Activity - Solution & Explanation

Problem Overview: The UserActivity table stores activities performed by users with start and end dates. For each user, return the second most recent activity based on endDate. If a user has only one activity, return that single record. The challenge is ranking activities per user while handling the edge case where only one row exists.

Approach 1: Window Function Ranking (ROW_NUMBER) (Time: O(n log n), Space: O(n))

The cleanest solution uses SQL ROW_NUMBER() with partitioning. Partition rows by username and order each partition by endDate DESC. This assigns rank 1 to the most recent activity and 2 to the second most recent. After ranking, filter rows where row_number = 2. To handle users with only one activity, compute COUNT(*) OVER (PARTITION BY username) and return the row with rank 1 when the count equals 1. Window functions allow you to compute ranking and counts in a single scan while keeping rows intact. This approach is standard in modern database interview questions and works efficiently in MySQL 8+ using SQL analytic features.

Approach 2: Aggregation + Self Join (Time: O(n log n), Space: O(n))

If window functions are unavailable, you can simulate the logic with aggregation and joins. First determine the most recent activity for each user using MAX(endDate). Then find the second largest endDate by filtering rows where the date is less than the maximum and taking another MAX. Join these results back to the original table to retrieve the full activity row. For users with only one activity, detect the single-row case using COUNT(*) per user and return that row directly. This method relies on multiple grouping and join steps, which makes it more verbose and harder to maintain than window functions.

Recommended for interviews: The window function approach using ROW_NUMBER() is what interviewers expect. It demonstrates familiarity with ranking operations and partitioning in database queries. Showing the aggregation approach first proves you understand the underlying logic, but the window function version is shorter, clearer, and considered the optimal SQL solution.