Leetcode Problem 1369. Get the Second Most Recent Activity

1369. Get the Second Most Recent Activity

Leetcode Solutions

Using Window Functions for Second Most Recent Activity

  1. Use the ROW_NUMBER() window function to assign a rank to each activity for each user, ordered by startDate in descending order.
  2. Partition the data by username so that each user's activities are ranked separately.
  3. Create a subquery that selects all columns along with the row number as rnk.
  4. From the subquery, select the rows where rnk is equal to 2, which represents the second most recent activity.
  5. Include the activities where the user has only one activity by checking if the maximum row number for a user is 1.
  6. Combine the results using the UNION ALL operator to ensure that both conditions are met.

erDiagram
    UserActivity {
        varchar username
        varchar activity
        date startDate
        date endDate
    }

Using Self-Join and Group By for Second Most Recent Activity

Ask Question

Programming Language
image/screenshot of info(optional)
Full Screen
Loading...

Suggested Answer

Answer
Full Screen
Copy Answer Code
Loading...