Skip to content

1369. Get the Second Most Recent Activity 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
WITH
  RankedUserActivity AS (
    SELECT
      *,
      COUNT(*) OVER(PARTITION BY username) AS `count`,
      RANK() OVER(
        PARTITION BY username
        ORDER BY endDate DESC
      ) AS `rank`
    FROM UserActivity
  )
SELECT
  username,
  activity,
  startDate,
  endDate
FROM RankedUserActivity
WHERE `count` = 1 OR `rank` = 2;