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

Question: leetcode.com/p...
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

[@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));

[@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.

[@user-bx5uk7xh4q]

yes you can do it by union
should be like that if u want to capture a user that has one activity
select username ,activity,startdate,end_date
from useractivity
group by username ,activity,startdate,end_date
having count(*)=1

[@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

[@james-r685]

This will fail when you have second recent record as duplicate

[@julianandresrioscano8124]

Very clear

[@EverydayDataScience]

Glad that you found the video useful.

[@roymou5856]

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

[@jatinyadav6158]

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()