Get the Second Most Recent Activity - Practice Coding Problems

Get the Second Most Recent Activity - Problem

Database Hard

Given a table UserActivity that contains information about user activities over time periods, write a SQL query to find the second most recent activity for each user.

Special Rule: If a user has only one activity, return that single activity.

Each activity has a startDate and endDate, and users cannot perform multiple activities simultaneously. The most recent activity is determined by the latest startDate.

Table Schema

UserActivity

Column Name	Type	Description
`username`	varchar	Username of the person performing the activity
`activity`	varchar	Name of the activity performed
`startDate`	date	Start date of the activity
`endDate`	date	End date of the activity

Primary Key: none

Note: Table may contain duplicate rows. Users cannot perform multiple activities at the same time.

Input & Output

Example 1 — Multiple Activities Per User

Input Table:

username	activity	startDate	endDate
Alice	Travel	2020-02-11	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. Ranked by startDate: Study (1st), Dancing (2nd), Travel (3rd). We select Dancing as the second most recent. Bob has only 1 activity, so we return that single Travel activity.

Example 2 — All Users Have Single Activity

Input Table:

username	activity	startDate	endDate
Alice	Travel	2020-02-11	2020-02-20
Bob	Study	2020-02-15	2020-02-18

Output:

username	activity	startDate	endDate
Alice	Travel	2020-02-11	2020-02-20
Bob	Study	2020-02-15	2020-02-18

💡 Note:

Both Alice and Bob have only one activity each. According to the rule, when a user has only one activity, we return that activity as their 'second most recent'.

Example 3 — Mixed Cases

Input Table:

username	activity	startDate	endDate
Alice	Travel	2020-02-11	2020-02-20
Alice	Dancing	2020-02-21	2020-02-23
Bob	Study	2020-02-15	2020-02-18
Charlie	Swimming	2020-02-10	2020-02-12
Charlie	Reading	2020-02-13	2020-02-15
Charlie	Cooking	2020-02-16	2020-02-18

Output:

username	activity	startDate	endDate
Alice	Travel	2020-02-11	2020-02-20
Bob	Study	2020-02-15	2020-02-18
Charlie	Reading	2020-02-13	2020-02-15

💡 Note:

Alice has 2 activities - Dancing is most recent, Travel is second most recent. Bob has 1 activity - return Study. Charlie has 3 activities - Cooking is most recent, Reading is second most recent.

Constraints

username and activity are non-empty strings
startDate ≤ endDate for all activities
Users cannot perform multiple activities simultaneously
Table may contain duplicate rows

Visualization

Tap to expand

Understanding the Visualization

Input

UserActivity table with multiple activities per user

Ranking

ROW_NUMBER() partitioned by username, ordered by startDate DESC

Filtering

Select rank=2 OR (rank=1 AND total_activities=1)

Key Takeaway

🎯 Key Insight: Use window functions to rank activities per user, handling both normal cases (rank=2) and edge cases (single activity)

Asked in

A Amazon 28 M Microsoft 15 F Facebook 12

Use window functions with ROW_NUMBER() OVER (PARTITION BY username ORDER BY startDate DESC) to rank activities by recency, then select rank 2 or rank 1 if only one activity exists.

Table Schema

UserActivity

Column Name	Type	Description
`username`	varchar	Username of the person performing the activity
`activity`	varchar	Name of the activity performed
`startDate`	date	Start date of the activity
`endDate`	date	End date of the activity

Primary Key: none

Note: Table may contain duplicate rows. Users cannot perform multiple activities at the same time.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with ROW_NUMBER	O(n log n)	O(n)	Use ROW_NUMBER() to rank activities by startDate for each user

Window Function with ROW_NUMBER — Algorithm Steps

Step 1: Use ROW_NUMBER() OVER (PARTITION BY username ORDER BY startDate DESC) to rank activities
Step 2: Count total activities per user with window function
Step 3: Select activities where rank=2 OR (rank=1 AND total_count=1)

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition

Group activities by username

Rank

Order by startDate DESC and assign row numbers

Filter

Select rank 2 or rank 1 if only one activity

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for ranking within each user partition

⚡ Linearithmic

Space Complexity

O(n)

Window function intermediate results

⚡ Linearithmic Space

34.5K Views

Medium Frequency

~18 min Avg. Time

892 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with ROW_NUMBER — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler