Tredence SQL Interview Question - Find events with 3 or more consecutive years for each pid

SQL question recently asked in Tredence interview. Find events with 3 or more consecutive years for each pid
To find out the expected output, I use row_number() function.
--Create table syntax
CREATE TABLE events ( pid INT, year INT )
-- Insert data into the table
INSERT INTO events VALUES (1, 2019), (1, 2020), (1, 2021), (2, 2022), (2, 2021),(3, 2019), (3, 2021), (3, 2022)
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@riteshhraj8033]

My Approach->
with cte as (select *,lag(year,1) over(partition by pid order by (select null)) as prev from events_tbl),
cte2 as(select *,ifnull((year-prev),1) as diff from cte)
select pid from cte2 group by pid having count(distinct diff)=1;
(I got sweat during solving this my myself).engineers do hell of a task.hats off

[@hairavyadav6579]

My approach
with cte as(select *,lead(year) over(partition by pid order by year) as rnk1,lead(year,2) over(partition by pid order by year) as rnk2 from events)
select distinct pid from cte where year+1=rnk1 and rnk1+1=rnk2;

[@CloudChallengers]

@hairavyadav6579, what if there are 4 consecutive years?

[@hairavyadav6579]

@@CloudChallengers
But we need only 3 so I use this

[@CloudChallengers]

@@hairavyadav6579, I told 3 or more

[@sravankumar1767]

Nice explanation bro 👍 👌 👏

[@333Stan]

my approach-
with cte as (select *, (nxtyr-yearr) as yrdiff from (
select *, lead(yearr,1,yearr+1)over(partition by pid order by yearr) as nxtyr from eventss) as a)
select pid from cte group by pid having count(yrdiff)>=3 and yrdiff=1;

[@ayushjain5550]

My approach -
with cte as(select *, max(year) over(partition by pid) as max_year, min(year) over(partition by pid) as min_year,
count(1) over(partition by pid) as cnt from events)
select distinct pid, concat(min_year, '-', max_year) as years from cte
where max_year - min_year < cnt and cnt > 2

[@ChaitanyaKariya-x4q]

select distinct pid from (
select pid, count(*) over(partition by diff,pid) as total from (
select *,
year - rnk as diff
from (
select *,
row_number() over(partition by pid order by year) as rnk
from events) x) y)z
where total >= 3

[@NikhilRaj-ub9cw]

My Solution:
with cte as(
Select *,
coalesce(lag(year) Over(partition by pid order by year),year-1) as prev_row
from
events1),
cte2 as(
select
*,year-prev_row as diff
from
cte where (year-prev_row)=1)
select pid,count(diff) from cte2 group by pid having count(diff)>=3

[@guruprasad7330]

Bro put video today for mphasis bro

[@Savenature635]

Here is my approach. :
with cte as (select *,lag(year,1,`year`-1) over(partition by pid) as previous_year
from p_events)
select pid
from cte
group by 1 having count(case when year-previous_year=3;

[@user-gq6cg3ls7f]

My Approach
with cte as(
select *,
count(*) over (partition by pid order by (select null)) cnt,
case when LEAD(year,1,year) over (partition by pid order by (select null)) = LAG(year,1,year) over (partition by pid order by (select null)) + 1 then 1 else 0 end bucket
from events_sunday
), cte2 as(
select pid, sum(bucket) bucket from cte where cnt=3
group by pid
)
select pid from cte2 where bucket>=2

[@CloudChallengers]

@user-gq6cg3ls7f, Thanks for sharing the alternative approach.

[@musicallywandering4617]

WITH CTE AS
(
SELECT PID, YEAR,
YEAR-ROW_NUMBER () OVER (PARTITION BY PID ORDER BY YEAR) AS DIFF
FROM EVENTSS
)
SELECT DISTINCT PID
FROM
(
SELECT PID, COUNT(*) OVER (PARTITION BY PID, DIFF) AS CNT
FROM CTE
) CTE_1
WHERE CNT>2;

[@landchennai8549]

; with cte
as
(
SELECT *,year-row_number() over ( partition by pid order by (select 1)) as Grp
FROM #events
)
select pid--, count(1) as count
from cte
group by pid,Grp
having count(1) > 2

[@prajju8114]

with cte_yr as
(
select *,lead(year) over(partition by pid order by year) as next_year, lag(year) over(partition by pid order by year) as prev_year from events
),
cte_2 as
(
select pid, year, datediff(day,year,next_year) as datediff_1, datediff(day,prev_year,year) as date_diff_2 from cte_yr
),
cte_3 as
(
select pid,coalesce(datediff_1,date_diff_2) as date_diff from cte_2
)
select pid from cte_3 group by pid,date_diff having count(*)>=3

[@jeevithacsdevanga2204]

with cte as (SELECT pid, year,LAG(year,1) OVER(partition by pid) prev_,
LEAD(year,1) OVER(partition by pid) next_
FROM events)
SELECT pid from cte
where next_=year+1 and prev_=year-1