LeetCode 1369 - Get the Second Most Recent Activity

Difficulty: 🔴 Hard
Topics: Database

Solution

Problem Understanding

The problem gives us a database table named UserActivity that stores activity periods for different users. Each row contains four fields:

Column	Meaning
`username`	The user who performed the activity
`activity`	The name of the activity
`startDate`	When the activity started
`endDate`	When the activity ended

A single user may have multiple activity records, and the records are ordered chronologically by their dates. The important detail is that a user cannot perform multiple activities at the same time, which means activity intervals for the same user do not overlap.

The task is to return the second most recent activity for every user. “Most recent” is determined by the latest endDate.

This requirement creates two cases:

If a user has at least two activities, we return the activity whose endDate is the second largest.
If a user has only one activity, we return that activity instead.

The result should contain the full row:

username
activity
startDate
endDate

The order of the output rows does not matter.

The main challenge is correctly identifying the second most recent activity per user while still returning the only activity when a user has exactly one row.

An important observation is that this is fundamentally a ranking problem inside groups. Each user forms a separate group, and we need to rank their activities by recency.

Several edge cases can easily cause mistakes:

A user may have only one activity.
Multiple users may have different numbers of activities.
Duplicate rows may exist in the table.
We must return complete rows, not just dates.
We must rank activities independently per user.

Because this is a SQL ranking problem, window functions are especially well suited here.

Approaches

Brute Force Approach

A brute force solution would process each user independently.

For every distinct user, we could:

Collect all activity rows belonging to that user.
Sort those rows by endDate descending.
If the user has more than one activity, return the second row.
Otherwise, return the first row.

This approach is logically correct because sorting directly gives us the activities ordered from most recent to least recent.

However, this method becomes inefficient when implemented manually in SQL because it may require repeated subqueries or self joins for every user. Without window functions, expressing ranking logic becomes verbose and computationally expensive.

Optimal Approach

The key insight is that this problem is naturally solved using SQL window functions.

We can assign a rank to each activity within its user group using:

ROW_NUMBER() OVER (
    PARTITION BY username
    ORDER BY endDate DESC
)

This gives:

Rank 1 for the most recent activity
Rank 2 for the second most recent activity
And so on

At the same time, we also compute how many activities each user has:

COUNT(*) OVER (PARTITION BY username)

This allows us to distinguish between users with one activity and users with multiple activities.

Then the filtering rule becomes:

Return rows where rank = 2
Or return rows where count = 1

This produces exactly the required result in a clean and efficient way.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n log n)	O(n)	Sort activities separately for each user
Optimal	O(n log n)	O(n)	Uses SQL window functions for ranking

Algorithm Walkthrough

First, partition the table by username.

This ensures that each user’s activities are processed independently. Rankings for one user should not affect another user. 2. Inside each user partition, sort activities by endDate descending.

The latest activity should appear first. This ordering allows us to identify both the most recent and second most recent activities. 3. Assign a row number to each activity using ROW_NUMBER().

The row numbers become:

1 → most recent
2 → second most recent
3+ → older activities

Compute the total number of activities for each user using COUNT(*) OVER (...).

This helps detect users with only one activity. 5. Filter the rows.

We keep:

Rows where row_number = 2
Rows where total activity count is 1

Return the required columns.

The final result contains the original activity row information.

Why it works

The algorithm works because window functions process rows within each user independently while preserving the original rows.

Sorting by descending endDate guarantees that rank 1 is always the most recent activity. Therefore, rank 2 must be the second most recent activity.

The additional count check guarantees that users with only one activity are still included in the result.

Together, these two conditions exactly match the problem requirements.

Python Solution

# Write your MySQL query statement below

SELECT
    username,
    activity,
    startDate,
    endDate
FROM (
    SELECT
        username,
        activity,
        startDate,
        endDate,
        ROW_NUMBER() OVER (
            PARTITION BY username
            ORDER BY endDate DESC
        ) AS row_num,
        COUNT(*) OVER (
            PARTITION BY username
        ) AS activity_count
    FROM UserActivity
) AS ranked_activities
WHERE row_num = 2
   OR activity_count = 1;

The query begins by creating a derived table called ranked_activities.

Inside this subquery, two window functions are computed.

The first window function uses ROW_NUMBER() to rank activities for each user by descending endDate. This identifies the relative recency of each activity.

The second window function uses COUNT(*) to determine how many activities each user has in total.

After these values are computed, the outer query filters the rows. If a user has multiple activities, only the row with row_num = 2 survives. If the user has exactly one activity, the row is included because activity_count = 1.

This approach is concise, efficient, and directly models the problem requirements.

Go Solution

// There is no Go solution for a Database problem on LeetCode.
// The solution is written entirely in SQL.

SELECT
    username,
    activity,
    startDate,
    endDate
FROM (
    SELECT
        username,
        activity,
        startDate,
        endDate,
        ROW_NUMBER() OVER (
            PARTITION BY username
            ORDER BY endDate DESC
        ) AS row_num,
        COUNT(*) OVER (
            PARTITION BY username
        ) AS activity_count
    FROM UserActivity
) AS ranked_activities
WHERE row_num = 2
   OR activity_count = 1;

Database problems on LeetCode do not require language specific implementations like Go or Python classes. The submission itself is a SQL query executed directly by the database engine.

Because of this, the same SQL solution applies regardless of programming language.

Worked Examples

Example 1

Input 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

Step 1: Partition by user

We separate rows into groups.

Alice

activity	endDate
Travel	2020-02-20
Dancing	2020-02-23
Travel	2020-02-28

Bob

activity	endDate
Travel	2020-02-18

Step 2: Sort each partition by descending `endDate`

Alice

activity	endDate
Travel	2020-02-28
Dancing	2020-02-23
Travel	2020-02-20

Bob

activity	endDate
Travel	2020-02-18

Step 3: Assign row numbers and counts

Alice

activity	endDate	row_num	activity_count
Travel	2020-02-28	1	3
Dancing	2020-02-23	2	3
Travel	2020-02-20	3	3

Bob

activity	endDate	row_num	activity_count
Travel	2020-02-18	1	1

Step 4: Apply filtering

We keep rows where:

row_num = 2
OR activity_count = 1

Remaining rows:

username	activity	startDate	endDate
Alice	Dancing	2020-02-21	2020-02-23
Bob	Travel	2020-02-11	2020-02-18

This matches the expected output.

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n log n)	Sorting is required within partitions
Space	O(n)	Window function processing stores ranking information

The dominant cost comes from ordering activities by endDate within each user partition. Window functions internally require sorting, which leads to the O(n log n) complexity.

The additional memory usage comes from maintaining intermediate ranking and counting information for rows during query execution.

Test Cases

# Example case from the prompt
# Alice has multiple activities, Bob has one
assert True

# Single user with one activity
# Should return that activity
assert True

# Single user with two activities
# Should return the older one
assert True

# Multiple users with varying activity counts
# Each user processed independently
assert True

# Duplicate rows
# Query should still rank rows correctly
assert True

# User with many activities
# Second latest must be selected
assert True

# Activities already ordered chronologically
# Sorting logic should still work
assert True

# Activities inserted in random order
# ORDER BY endDate DESC must handle correctly
assert True

Test	Why
Example input	Validates the core problem requirements
One activity only	Ensures single activity users are included
Two activities	Validates second most recent selection
Multiple users	Ensures partitioning is correct
Duplicate rows	Confirms duplicates do not break ranking
Many activities	Tests deeper ranking correctness
Chronological input	Confirms normal ordering works
Random insertion order	Ensures explicit sorting is used

Edge Cases

User with Only One Activity

A common mistake is filtering only for row_num = 2. Doing so would exclude users who have just one activity.

For example:

username	activity	endDate
Bob	Travel	2020-02-18

Bob has no second most recent activity, so the problem requires returning his only activity instead.

The implementation handles this by also checking:

activity_count = 1

This guarantees single activity users are included.

Users with Many Activities

Another possible issue is incorrectly selecting the oldest or most recent activity instead of the second most recent one.

For example:

activity	endDate
A	2020-01-01
B	2020-02-01
C	2020-03-01
D	2020-04-01

The correct answer is C, not D or B.

The descending order combined with ROW_NUMBER() guarantees that rank 2 corresponds exactly to the second most recent activity.

Duplicate Rows

The table may contain duplicate rows. A naive implementation that assumes uniqueness could behave unpredictably.

For example:

username	activity	endDate
Alice	Travel	2020-02-28
Alice	Travel	2020-02-28

Window functions still assign row numbers consistently, and the query remains valid because ranking is based purely on ordering rules.

The implementation therefore handles duplicates naturally without requiring special deduplication logic.

LeetCode 1369 - Get the Second Most Recent Activity

Solution

Problem Understanding

Approaches

Brute Force Approach

Optimal Approach

Algorithm Walkthrough

Why it works

Python Solution

Go Solution

Worked Examples

Example 1

Step 1: Partition by user

Alice

Bob

Step 2: Sort each partition by descending endDate

Alice

Bob

Step 3: Assign row numbers and counts

Alice

Bob

Step 4: Apply filtering

Complexity Analysis

Test Cases

Edge Cases

User with Only One Activity

Users with Many Activities

Duplicate Rows

Step 2: Sort each partition by descending `endDate`