24 - Data Engineering Interview Question , Leetcode Problem - 1369 | Hard Level

#sqlserver #sqlinterviewquestions #sql

𝗦𝗰𝗿𝗶𝗽𝘁 𝗳𝗼𝗿 𝗽𝗿𝗮𝗰𝘁𝗶𝗰𝗲:

drop table if exists UserActivity;

create table UserActivity (username varchar(10) , activity varchar(10) , startDate date , endDate date);
insert into UserActivity values ('Alice' , 'Travel' , '2020-02-12' , '2020-02-20');
insert into UserActivity values ('Alice' , 'Dancing' , '2020-02-21' , '2020-02-23');
insert into UserActivity values ('Alice' , 'Travel' , '2020-02-24' , '2020-02-28');
insert into UserActivity values ('Bob' , 'Travel' , '2020-02-11' , '2020-02-18');

select * from UserActivity;

#sql #analytics

[saikatde]

Please like this video if you find the problem interesting. 🙂

[GowthamR-ropt]

Hi Saikat, my approach will be :
with cte as (select username, activity, startDate, endDate, ROW_NUMBER() over (partition by username order by enddate)rn from UserActivity), cte2 as (
select *, ROW_NUMBER() over (partition by username order by enddate desc)rn1 from cte
where rn in(1, 2))
select * from cte2
where rn1 = 1