Get the Second Most Recent Activity - Practice Coding Problems

Get the Second Most Recent Activity - Problem

Database Hard

Imagine you're building a user activity dashboard that tracks what your users have been up to lately. You have a database table called UserActivity that logs every activity a user performs along with start and end dates.

Your mission: Find the second most recent activity for each user. But here's the catch - if a user only has one activity on record, that single activity should be returned instead.

Think of it like looking at someone's recent history - you want to see what they did before their most recent action, but if they're a new user with only one activity, that's all you can show.

Table Structure:

Column Name	Type
username	varchar
activity	varchar
startDate	Date
endDate	Date

Key Rules:

Users cannot perform multiple activities simultaneously
The table may contain duplicate rows
Return results in any order
For users with only 1 activity, return that activity
For users with 2+ activities, return the second most recent

Input & Output

example_1.sql — Basic Case

$ Input: UserActivity table: +----------+----------+------------+------------+ | username | activity | startDate | endDate | +----------+----------+------------+------------+ | Alice | Travel | 2020-02-12 | 2020-02-20 | | Alice | Dancing | 2020-02-21 | 2020-02-23 | | Alice | Study | 2020-02-24 | 2020-02-25 | | 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 | +----------+----------+------------+------------+

💡 Note: Alice has 3 activities. Her most recent is Study (ends 2020-02-25), so her second most recent is Dancing (ends 2020-02-23). Bob has only 1 activity, so we return his only activity: Travel.

example_2.sql — Multiple Users

$ Input: UserActivity table: +----------+----------+------------+------------+ | username | activity | startDate | endDate | +----------+----------+------------+------------+ | John | Coding | 2023-01-01 | 2023-01-02 | | John | Reading | 2023-01-03 | 2023-01-04 | | Mary | Gym | 2023-01-01 | 2023-01-02 | | Mary | Cooking | 2023-01-05 | 2023-01-06 | | Mary | Shopping | 2023-01-07 | 2023-01-08 | +----------+----------+------------+------------+

💡 Note: John has 2 activities: Reading (most recent) and Coding (second most recent). Mary has 3 activities: Shopping (most recent), Cooking (second most recent), Gym (oldest). We return the second most recent for both.

example_3.sql — Edge Case: All Single Activities

$ Input: UserActivity table: +----------+----------+------------+------------+ | username | activity | startDate | endDate | +----------+----------+------------+------------+ | Tom | Running | 2023-05-01 | 2023-05-01 | | Lisa | Painting | 2023-05-02 | 2023-05-03 | | Sam | Gaming | 2023-05-04 | 2023-05-05 | +----------+----------+------------+------------+

› Output: +----------+----------+------------+------------+ | username | activity | startDate | endDate | +----------+----------+------------+------------+ | Tom | Running | 2023-05-01 | 2023-05-01 | | Lisa | Painting | 2023-05-02 | 2023-05-03 | | Sam | Gaming | 2023-05-04 | 2023-05-05 | +----------+----------+------------+------------+

💡 Note: Each user has exactly one activity, so we return that single activity for each user. This tests the edge case where no user has a 'second' activity.

Visualization

Tap to expand

Understanding the Visualization

Scan User Activities

Read through all user activities with their timestamps

Rank by Recency

For each user, rank their activities: 1 = most recent, 2 = second most recent, etc.

Count Activities

Count how many total activities each user has performed

Apply Logic

If count = 1, take rank 1. If count > 1, take rank 2

Key Takeaway

🎯 Key Insight: Window functions with ROW_NUMBER() and COUNT() enable elegant handling of both single-activity users and multi-activity users in a single efficient query, avoiding complex subqueries and multiple table scans.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single table scan with sorting for ROW_NUMBER() window function

⚡ Linearithmic

Space Complexity

O(n)

Database needs to store temporary results for window function calculations

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁴
username contains only lowercase letters and numbers
activity is a non-empty string
startDate ≤ endDate for all rows
Users cannot have overlapping activities (no simultaneous activities)

Asked in

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

The optimal solution uses SQL window functions with ROW_NUMBER() to rank activities by end date for each user, combined with COUNT() to determine total activities per user. A single WHERE clause handles both cases: return rank 1 if user has only 1 activity, or rank 2 for users with multiple activities. This achieves O(n log n) performance with just one table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with ROW_NUMBER (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to rank activities by date, then filter for appropriate ranks
Brute Force (Multiple Subqueries)	O(n²)	O(1)	Use nested subqueries to find second most recent activity for each user

Window Function with ROW_NUMBER (Optimal) — Algorithm Steps

Use ROW_NUMBER() OVER (PARTITION BY username ORDER BY endDate DESC) to rank activities
Count total activities per user using COUNT() window function
Apply WHERE clause to filter: (activity_count = 1 AND rn = 1) OR (activity_count > 1 AND rn = 2)
Return the filtered results

Visualization

Tap to expand

Step-by-Step Walkthrough

Single Table Scan

Read each row once while applying window functions

ROW_NUMBER() Ranking

Assign rank 1, 2, 3... to activities per user by end date

COUNT() Per User

Calculate total activities per user in the same pass

Filter Results

Apply WHERE logic to get rank 1 (if count=1) or rank 2

Code -

solution.c — C

// SQL Solution (Optimal Window Function Approach)
#include <stdio.h>

const char* getOptimalQuery() {
    static const char query[] = 
        "WITH RankedActivities AS ("
        "    SELECT "
        "        username, "
        "        activity, "
        "        startDate, "
        "        endDate,"
        "        ROW_NUMBER() OVER (PARTITION BY username ORDER BY endDate DESC) as rn,"
        "        COUNT(*) OVER (PARTITION BY username) as activity_count"
        "    FROM UserActivity"
        ") "
        "SELECT username, activity, startDate, endDate "
        "FROM RankedActivities "
        "WHERE (activity_count = 1 AND rn = 1) "
        "   OR (activity_count > 1 AND rn = 2)";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single table scan with sorting for ROW_NUMBER() window function

⚡ Linearithmic

Space Complexity

O(n)

Database needs to store temporary results for window function calculations

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁴
username contains only lowercase letters and numbers
activity is a non-empty string
startDate ≤ endDate for all rows
Users cannot have overlapping activities (no simultaneous activities)

42.5K Views

High Frequency

~18 min Avg. Time

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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Function with ROW_NUMBER (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler