SQL Data Analysis Interview Question #37/100 | SQL Challenge | SQL Tutorials | Flipkart Interview

[@Soul-f3v]

with cte as (
select id,LAG(num) over(order by id) as pre_num,num,LEAD(num) over (order by id) as nxt_num from Logs)
select num from cte where pre_num=Num and Num=nxt_num;

[@Abhi4upskill]

with cte as(
select *,
lag(num)over(order by id)as prev,
lead(num)over(order by id)as next
from Flipkart_Logs
)
select num from cte where
num = 1 and (prev =1 or next = 1)
group by num
having count(*)>=3

[@user-gq6cg3ls7f]

with cte as(
select *,
case when num = LEAD(num) over (order by id) then 1 end next_row,
case when num = LEAD(num,2) over (order by id) then 1 end next_two_row
from Logs_Sat
)
select id from cte where (num=next_row) and (num=next_two_row)

[@mulikinatisiddarthasiddu8245]

❤🎉

[@hulhoop7197]

in the beginning of the video the output table shows we have to select the id for the consecutive streaks of numbers but here you have selected num, can you please check

[@zero_analyst]

We need to select num! which is second column!

[@hulhoop7197]

i think this will be a more logical approach in a general case. Would be nice to have your opinion on and thanks for everything you have been doing for us
WITH CTE1 AS
(
SELECT
id,
num,
ROW_NUMBER() OVER(ORDER BY id) AS rn,
ROW_NUMBER() OVER(PARTITION BY num) AS rn2
FROM Logs
)
SELECT
MIN(id) AS id
-- COUNT(*)
FROM CTE1
GROUP BY rn-rn2, num
HAVING COUNT(*) >=3