Get the Second Most Recent Activity - Problem

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
Second Most Recent Activity INPUT: UserActivity Table user activity startDate end Alice Travel 02-12 02-20 Alice Dance 02-21 02-23 Alice Paint 02-25 02-27 Bob Code 02-10 02-15 Bob Sport 02-18 02-22 Carol Read 02-05 02-08 Activity Counts: Alice: 3 activities Bob: 2 activities Carol: 1 activity (special case) If only 1 activity, return it ALGORITHM STEPS 1 ROW_NUMBER() Rank activities by startDate PARTITION BY username ORDER BY startDate DESC 2 COUNT() Count total activities per user COUNT(*) OVER (PARTITION BY username) 3 Filter Logic Keep row_num = 2 (2nd recent) OR total_count = 1 (only one) 4 WHERE Clause WHERE rn = 2 OR cnt = 1 Ranking Example (Alice): Paint rn=1, Dance rn=2, Travel rn=3 2nd most recent = Dance FINAL RESULT user activity dates Alice Dance 02-21 Bob Code 02-10 Carol Read 02-05 Why Carol appears: Carol has only 1 activity Special rule: return it as result Alice & Bob: Both have 2+ activities Return 2nd most recent (rn=2) Result: OK 3 rows returned correctly Key Insight: Window Functions for Ranking Use ROW_NUMBER() with PARTITION BY to rank each user's activities by date. Use COUNT() OVER to identify users with only one activity (special case handling). Combine conditions: WHERE row_number = 2 OR total_count = 1 covers all scenarios efficiently. TutorialsPoint - Get the Second Most Recent Activity | Optimal Solution (Window Functions)

Related Problems

Asked in
Amazon 28 Microsoft 15 Facebook 12
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