SQL Interview Questions – Get the Second Most Recent Activity

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

Solution –

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