Skip to main content
Talentd

Get the Second Most Recent Activity - Solution & Explanation

HardPremiumFree on FleetCodeDatabase4 min readAsked at: Microsoft
Practice this problem

Problem Statement

Table: UserActivity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
This table may contain duplicates rows.
This table contains information about the activity performed by each user in a period of time.
A person with username performed an activity from startDate to endDate.

 

Write a solution to show the second most recent activity of each user.

If the user only has one activity, return that one. A user cannot perform more than one activity at the same time.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
UserActivity table:
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Travel       | 2020-02-12  | 2020-02-20  |
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Alice      | Travel       | 2020-02-24  | 2020-02-28  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+
Output: 
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+
Explanation: 
The most recent activity of Alice is Travel from 2020-02-24 to 2020-02-28, before that she was dancing from 2020-02-21 to 2020-02-23.
Bob only has one record, we just take that one.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Window Function (ROW_NUMBER)O(n log n)O(n)Best modern SQL solution when window functions are supported
Aggregation + Self JoinO(n log n)O(n)Useful when the database does not support window functions

Video Solution

LeetCode Hard 1369 "Second Most Recent Activity" Microsoft Interview SQL Question with Explanation • Everyday Data Science • 11,012 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Get the Second Most Recent Activity easy or hard?
LeetCode classifies this problem as Hard because it requires understanding SQL window functions and handling edge cases when a user has only one activity. Once you know ROW_NUMBER with partitioning, the implementation becomes straightforward.
Get the Second Most Recent Activity Python/Java solution
This is a database problem, so the primary solution is written in SQL (typically MySQL). Python or Java would simply execute the SQL query against the database using a connector or ORM, while the ranking logic itself remains inside the SQL query.
How to solve Get the Second Most Recent Activity in O(n)?
Pure O(n) is generally not achievable because ranking by date requires sorting or ordered indexing. With proper indexing on (username, endDate), databases can reduce sorting overhead and approach near-linear performance, but the theoretical complexity remains around O(n log n).
What is the best approach for Get the Second Most Recent Activity?
The best approach uses the SQL window function ROW_NUMBER() partitioned by username and ordered by endDate descending. This ranks activities per user so the second most recent row has rank 2. Combine it with COUNT(*) OVER (PARTITION BY username) to return the only activity when a user has a single record.
Is Get the Second Most Recent Activity asked at Google/Amazon/Meta?
SQL ranking and window-function problems like this appear frequently in database interviews at companies such as Amazon, Meta, and Google. Interviewers use them to test knowledge of partitioning, ranking functions, and handling edge cases with grouped records.
What data structure is used in Get the Second Most Recent Activity?
The problem relies on relational database operations rather than traditional in-memory data structures. SQL window functions such as ROW_NUMBER and COUNT with partitioning act like ranking structures over grouped datasets.
What is the time complexity of Get the Second Most Recent Activity?
The query typically runs in O(n log n) time because the database must sort activities within each user partition by endDate. Window functions like ROW_NUMBER perform ranking after sorting. Space complexity is O(n) for intermediate query results.

Ready to solve this problem?

Practice Get the Second Most Recent Activity with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary