Problem Description –
Write an SQL query to show the second most recent activity of each user.
If the user only has one activity, return that one. A user cannot perform more than one activity at the same time.
Return the result table in any order.
The query result format is in the following example.
Difficulty Level – Hard
Problem Link – Second Most Recent Activity
SELECT username, activity, startDate, endDate FROM ( SELECT *, DENSE_RANK() OVER(PARTITION BY username ORDER BY startDate DESC) as rnk, COUNT(activity) OVER(PARTITION BY username) as cnt FROM UserActivity ) x WHERE rnk = 2 OR cnt < 2