Indium SQL Real-Interview Question | Medium Level | Important

[@user-gq6cg3ls7f]

with cte as(
select *,
LAG(num) over (order by id) pre_row,
LEAD(num) over (order by id) next_row
from logs
)
select num as consecutive_number from cte where (num=pre_row) and (pre_row=next_row)

[@rk-ej9ep]

Nice..Thanks for your efforts..

[@rohit_vora]

postgresql :
with cte as
(select *,
(id - row_number() over(partition by cnt order by id )) rn
from
(select *
,count(*) over(partition by num order by id
range between unbounded preceding and unbounded following) cnt
from logs))
select distinct num
from
(select *, count(rn) over(partition by rn order by id
range between unbounded preceding and unbounded following) final_cnt
from cte)
where final_cnt >=3

[@nishchaljain9351]

Another approach to solve it is as below..
select Num as consecutive_num from
(
select *,
LEAD(Num,1,Num) over(order by id) as next_num,
LEAD(Num,2) over(order by id) as next_to_next_num,
case when Num = LEAD(Num,1,Num) over(order by id) and Num = LEAD(Num,2) over(order by id) then 1 else 0 end as flag
from Logs
) x where x.flag = 1

[@badrilalnagar9232]

God's love is reserved only for the virtuous and the dutiful.

[@hairavyadav6579]

My approach
select distinct num from (select *,lead(num) over(order by id) as l1 , lead(num,2) over(order by id) as l2 from logs) sal where num = l1 and l1=l2;

[@anshusharaf2019]

WITH cte AS (
SELECT id, num,
LEAD(num) OVER(ORDER BY id) AS next_num
FROM logs
), cte2 AS (
SELECT num, (next_num - num) AS diff
FROM cte
)
SELECT num, COUNT(diff) AS cnt
FROM cte2
WHERE diff IS NOT NULL
GROUP BY num;
My approach little bit different I used only CTE's and Count(), group By

[@hairavyadav6579]

Please mam ,, Bring one project on real scenario based , with 15 questions ..

[@vaibhavyadav2499]

SELECT num AS consecutive_nums
FROM logs
GROUP BY num
HAVING COUNT(DISTINCT id) >=3 AND MAX(id) - MIN(id) =2;

[@ShobhitSharma-hq7tz]

This question asked for fresher or experience

[@humdrum2041]

Hi is there any way you could do that without creating those extra tables?

[@amanrawat7894]

Thoda bhut Hindi language use kar doge sarkar??😅

[@hairavyadav6579]

select distinct l1.num from logs l1 join logs l2 on l1.id=l2.id-1 join logs l3 on l1.id=l3.id-2 where l1.num = l2.num and l2.num = l3.num;