with cte as( select id,num,LEAD(num) over(order by id)as nxt_num from Series), nxt_nxt as( select id,num,nxt_num,LEAD(nxt_num) over (order by id) as nxt_nxt from cte) select distinct num from nxt_nxt where num = nxt_num and nxt_num=nxt_nxt;
with cte as ( select cast(id as signed) as id,num, cast(row_number() over(order by num) as signed) rn from Series ), cte2 as ( select *, abs(id - rn) as cnt from cte ) select num from cte2 group by num, cnt having count(*) > 2
with cte as( select *,lag(num,1) over(order by id) as ld, lead(num,1) over(order by id) as led from Series) select distinct num from cte where num=ld or num=led With the help of Window function.
Great ques, i first solved then i watched your solution , so this is a great way to practice.
Thanks, I am going to solve this question with the help of windows function too. In the upcoming session stay tune👍
@@deepankarpathak983 sure waiting
thanks please keep posting
Thanks, please share among your friends 👍
with cte as(
select id,num,LEAD(num) over(order by id)as nxt_num from Series),
nxt_nxt as(
select id,num,nxt_num,LEAD(nxt_num) over (order by id) as nxt_nxt from cte)
select distinct num from nxt_nxt where num = nxt_num and nxt_num=nxt_nxt;
with cte as
(
select
cast(id as signed) as id,num,
cast(row_number() over(order by num) as signed) rn from Series
),
cte2 as
(
select *,
abs(id - rn) as cnt
from cte
)
select num
from cte2
group by num, cnt
having count(*) > 2
with cte as(
select *,lag(num,1) over(order by id) as ld,
lead(num,1) over(order by id) as led
from Series)
select distinct num from cte where num=ld or num=led
With the help of Window function.
That's great, please try without Windows Function too.
10-Oct-2024