Leetcode Hard SQL Problem - 6 | Second Most Recent Activity | SQL Window Analytical Functions

In this video we will silve a leetcode hard problem 1369 . Where we need to find second most recent activity and if user has only 1 activoty then return that as it is. We will use SQL window functions to solve this problem.

Here is the script:

create table UserActivity
(
username varchar(20) ,
activity varchar(20),
startDate Date ,
endDate Date
);

insert into UserActivity values
('Alice','Travel','2020-02-12','2020-02-20')
,('Alice','Dancing','2020-02-21','2020-02-23')
,('Alice','Travel','2020-02-24','2020-02-28')
,('Bob','Travel','2020-02-11','2020-02-18');

#sql #interview #window #functions #rank #count

[Dhanushts-gx]

with cte1 as (select *, row_number() over(partition by username order by startdate) rnk
from useractivity)
(select username, activity, startdate, enddate from cte1 where rnk=2)
union (select * from useractivity group by username having count(*)=1)
(simple one)

[mantisbrains]

select * from useractivity where username in
(select username from useractivity
group by username
having count(username) = 1)
union
select username, activity, startdate, enddate from
(select *, dense_rank() over (partition by username order by startdate)rnk from
useractivity)a
where rnk =2;

Thank you, Ankit !

[akashdeep]

Hey ankit, first of all really thnxu for such amazing content

I have also solved by myself using nested query the answer is correct but i think my answer can become cumbersome is the activities are more in no

select username, activity, startDate, endDate from (
select *,
rank()over(partition by username order by startDate desc)as rn_date from(
select username, activity, startDate, endDate from (
select username, activity, startDate, endDate,
rank()over(partition by username order by startDate )rn_date from UserActivity)a
where rn_date in (1, 2))b)c
where rn_date=1

[MIlanPaudel-wj]

with cet as(
select *,
dense_rank() over(partition by username order by startDate ) as rnk,
count(username) over(partition by username) as cnt
from dbo.UserActivity)

select username, activity, startdate, endDate from cet
where rnk=2
union all
select username, activity, startdate, endDate from cet
where cnt=1

[yadavikasAnalyst]

Another MYSQL solution:

SELECT
username,
activity,
startDate,
endDate
FROM
(
SELECT
*,
RANK()OVER(PARTITION BY username ORDER BY startDate) AS rnk
FROM useractivity
)a WHERE rnk=2
UNION ALL
(
SELECT
username,
MAX(activity),
MAX(startDate),
MAX(endDate)
FROM useractivity
GROUP BY username
HAVING COUNT(*)=1
);

[addhyasumitra]

With CTE as (
select *,
COUNT(*) over(partition by username) as activity_count,
rank() Over (partition by username order by startDate desc) as rn
from UserActivity)
Select username, activity, startDate, endDate
from CTE
where activity_count=1 or rn=2

[ayushkashyap]

select a.*, b.cnt
from (select *, rank() over(partition by username order by startdate)rnk
from useractivity)a
inner join
(select username, case when count(username)>1 then 2 else 1 end cnt
from useractivity
group by username)b
on a.username=b.username and a.rnk=b.cnt

[rishianand]

with cte as (
select *
, row_number() over(partition by username order by startDate asc) rn
, count(username) over(partition by username) activity_cnt
from sql_practice.UserActivity
qualify (rn=2 and activity_cnt>1) or (rn=1 and activity_cnt=1))
select username, activity, startDate, endDate from cte

[greatromulus]

Your solution is more elagant than mine, this is my solution ;

with cte as
(select username, activity, startDate, endDate
, row_number() over (partition by username order by startDate desc) as rn
, count(*) over (partition by username) as cnt
from UserActivity)

, cte2 as
(select u.*, case when cnt > 1 then rn else rn+1 end as filter
from cte u)

select username, activity, startDate, endDate
from cte2
where filter = 2

[senthilkumarjeyaraj]

Thanks man for your dedication on coming always with different kind of scenarios, Kudos to you 🥇



My
with cte as (
select *, row_number() over(partition by username order by startdate desc ) rn
from useractivity )
select * from cte where rn = 2
union all
select * from cte where username =
(select username from cte
group by username
having count(*)=1);

[bikki]

Hi Ankit,

Your Videos helping a lot to practice for interviews and as well to solve issues in work place. Thanks a lot for posting such a valuable information.

[sowmiyav]

Hi Ankit,

Here is my version of solution.

with total_activities as (
select username, count(*) as 'tot_act' from UserActivity
group by username)
, second_activity as (
select *, rank() over(partition by username order by startdate) as 'rnk'
from UserActivity)
select t.username, s.activity, s.startdate, s.enddate from total_activities t
inner join second_activity s on s.username=t.username
where s.rnk=2 or t.tot_act=1;


But your solution is great and simple😊

[nishantsalian]

Hey Ankit. Just wanted to mention one additional information that I saw is an edge case and might fail with this query. If there are more than 3 activities then the question is expecting for the second most recent activity which can have a rank > 2 which would be max(rn) -1. Thanks for all your videos!

[xpro]

With ct1 as(
Select *,
Rank() Over(Partition by username Order by startdate) as rn,
count(1) Over(Partition by username) as total_act
From UserActivity)

Select username, activity, startdate, enddate
From ct1
where rn = 2 or total_act = 1

[amitmay]

--Using subquery
select a.username, a.recent_date, b.activity
from
(select
username, max(enddate) recent_date
from userActivity
Where enddate < (select max(enddate) from userActivity)
group by username) a
JOIN userActivity b on a. username = b.username and a.recent_date = b.enddate

[Thetradersclub_]

Aapne toh leetcode ki beizzati kardi: let's discuss a hard problem but it's not very hard😅😅

[sahilummat]

Thanks Ankit
here is the approach i followed
with cte as (
select *,
rank() over (partition by username order by startdate desc)as rn
from UserActivity
)
, cte2 as (
select *,
case when COUNT(1)over(partition by username)=1 then 1
COUNT(1)over(partition by username)>1 then 2 end as rnk
from cte )
select cte2.username, cte2.activity, cte2.startdate, cte2.enddate
from cte2 where cte2.rn=cte2.rnk

[simplyVeer]

with cte as(
select *
, count(1) over (partition by username) total_activities
, RANK() over (partition by username order by startDate desc) rn
from UserActivity)

select username, activity, startDate, endDate
from cte
where rn = (case when total_activities > 1 then 2 else 1 end)

[simplecookingbynikita]

Select * from
(
select *,
count(1) over(partition by username) as cnt,
dense_rank() over (partition by username order by startdate ) as rnk from UserActivity)A
where rnk=2 or cnt=1

[rajorshi]

Very insightful explanation. Thanks for making SQL so easy to understand.