LeetCode Hard 1369 'Second Most Recent Activity' Microsoft Interview SQL Question with Explanation

In this video I solve and explain a hard difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #SQLinterviewQuestions #LeetcodeHard

[cheerfulchai]

Hi, why did we use the second window function here for count for num_of_activity? Can we do it other way without window function? Please guide.

[saisushanthdurvasulabitsian]

another approach using only one cte:
with cte as
(
select username
, activity
, startDate
, endDate
, dense_rank() over (partition by username order by endDate desc) as act_rank
, count(*) over (partition by username) as act_cnt
from UserActivity
)
select username
, activity
, startDate
, endDate
from cte
where act_rank = 2
or act_cnt = 1

[roymou]

select
username , activity , startDate , endDate
from
(
select *, rank() over(partition by username order by startDate desc) as rnk, count(activity) over(partition by username) as no_of_activity
from (select distinct * from UserActivity) t
) p
where rnk = 2 or no_of_activity = 1

[SchreiEs]

Another solution using SQL Server:

select
*
from
useractivity;

--Method 1

with cte
as
(
select
*
, count(activity) over(partition by username) as total_activities
, rank () over(partition by username order by startdate) as rnk
from
useractivity
)

, rnk2
as
(
select
*
from
cte
where
rnk = 2
)

, rnk1
as
(
select
*
from
cte
where
rnk = 1
)

(select
username
, activity
, startdate
, enddate
from
rnk2

union

select
username
, activity
, startdate
, enddate
from rnk1
where
username not in (select
distinct
username
from
rnk2));

[jatinyadav]

spark.sql("""

with cte1 as (
SELECT *, count(activity) over (partition by username) as count, rank() over(partition by username order by startDate DESC) as rank
from UserActivity
),
cte2 as(
SELECT username, activity, startDate, endDate, rank
from cte1
where count = 1
)
select username, activity, startDate, endDate
from cte1
where rank = 2
UNION ALL
select username, activity, startDate, endDate
from cte2
""").show()