LeetCode 1369: Get the Second Most Recent Activity [SQL]

Solution and walkthrough of leetcode database problem 1369: Get the Second Most Recent Activity. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

Playlists:

[esmas]

Thank you for your time and effort in making the questions and solutions understandable for us.

[VsEdits]

tooo good soln this playlist is gold 🙂🙌

[ajaxaj]

We can use lead function with case statement

[ankitgada]

Your explanation is simply awesome! Can you please do 579 - Cumulative Salary of Employee?

[shrutighoradkar]

select * from useractivity where username in(
select username from useractivity group by 1 having( count(username))=1)
union
select username, activity, startDate, endDate from(
SELECT username, activity, startDate, row_number() over(partition by username order by startDate desc) as lead_date
, endDate
FROM useractivity) a
where lead_date=2
order by username

[joshuakan]

Does this work? Love your videos btw

WITH CTE AS(
SELECT
username
, activity
, startDate
, endDate
, DENSE_RANK() OVER(PARTITION BY username ORDER BY endDate DESC) AS d_rnk
FROM userActivity
)



SELECT
username
, activity
, startDate
, MAX(endDate) AS endDate
FROM CTE
WHERE
d_rnk <=2
GROUP BY 1, 2, 3

[andreanlobo]

Used the following query:

SELECT
username,
activity,
startdate,
enddate
FROM
(SELECT
username,
activity,
startdate,
enddate,
DENSE_RANK() OVER(PARTITION BY username ORDER BY startdate) as rnk
FROM useractivity)t1
WHERE rnk = 2

UNION

SELECT
a.username,
b.activity,
b.startdate,
b.enddate
FROM
(SELECT
username
FROM useractivity
GROUP BY 1
HAVING COUNT(username) = 1) a
INNER JOIN useractivity b
ON a.username = b.username