select distinct(l1.num) as consecutive_num from series as l1,series as l2,series as l3 where l1.id = l2.id -1 and l2.id = l3.id-1 and l1.num = l2.num and l2.num = l3.num
Took me 40min to wreck my brain but got this that will solve for any length: ;with cte as ( -provide previous and following num Select id, num, lag(num) over (order by ID) prev_num, lead(num) over (order by id) lead_num from Series ), cte2 as ( -filter those that don’t have matching either previous or following num, find starting ID SELECT * , case when prev_num is null OR prev_num num then ID end start_id FROM CTE where lead_num=num or prev_num = num ), CTE3 AS( -create group_id for each consecutive ID with same num, max() used as IDs grow consecutively select ID,num , max(start_id) over (order by id) Group_ID from cte2 ) -final filter - group_id that has bigger than 2 count SELECT * FROM CTE3 A WHERE Group_ID IN (select Group_ID from cte3 GROUP BY Group_ID HAVING COUNT(1) > 2)
with cte as (select * , (id -row_number() over(partition by cnt order by id )) rn from (select * ,count(num) over(partition by num) cnt from series order by 1) x ) select id from (select * , count(rn) over(partition by rn) finalcnt from cte) a where a.finalcnt > 1
but this is not that efficent approach ....what ifthe question will be asked to see 5/6/7 consecutive numbers ? Make the sql query in such that it will cover all edge cases .Please make a video on this
Yes, this will not handle this case. As the problem statement is different, but I'll try to publish a video on this case too. Thanks for the suggestions and happy learning 👍
10 oct 2024
select distinct(l1.num) as consecutive_num
from series as l1,series as l2,series as l3
where l1.id = l2.id -1
and l2.id = l3.id-1
and l1.num = l2.num
and l2.num = l3.num
Took me 40min to wreck my brain but got this that will solve for any length:
;with cte as (
-provide previous and following num
Select id, num,
lag(num) over (order by ID) prev_num,
lead(num) over (order by id) lead_num
from Series
),
cte2 as (
-filter those that don’t have matching either previous or following num, find starting ID
SELECT * ,
case when prev_num is null OR prev_num num then ID end start_id
FROM CTE
where
lead_num=num or prev_num = num
),
CTE3 AS(
-create group_id for each consecutive ID with same num, max() used as IDs grow consecutively
select ID,num , max(start_id) over (order by id) Group_ID
from cte2
)
-final filter - group_id that has bigger than 2 count
SELECT * FROM CTE3 A
WHERE Group_ID IN (select Group_ID from cte3 GROUP BY Group_ID HAVING COUNT(1) > 2)
That's great 👍
with cte as
(select *
, (id -row_number() over(partition by cnt order by id )) rn
from
(select *
,count(num) over(partition by num) cnt
from series
order by 1) x )
select id
from
(select *
, count(rn) over(partition by rn) finalcnt
from cte) a
where a.finalcnt > 1
but this is not that efficent approach ....what ifthe question will be asked to see 5/6/7 consecutive numbers ? Make the sql query in such that it will cover all edge cases .Please make a video on this
Yes, this will not handle this case.
As the problem statement is different, but I'll try to publish a video on this case too.
Thanks for the suggestions and happy learning 👍