FLIPKART DATA ANALYST INTERVIEW QUESTION | Numbers

[@PavaniReddy-m2q]

with cte as
(select id,v_numb ,dense_rank() over (order by v_numb) rnk from SequenceNumbers)
select v_numb from cte
group by v_numb having count(rnk)>1

[@hairavyadav6579]

with cte as (select *,lag(Number) over(order by id) as n1, lag(number,2) over(order by id) as n2 from num)
select distinct number from cte where Number=n1 and n1=n2;

[@themightyvk]

With cte as(
select *, lag(number,1) over(order by id) as prev1,
lag(number,2) over(order by id) as prev2
from SequenceNumbers)
select distinct number from cte where number = prev1 and number = prev2

[@Abhi4upskill]

with cte as (
select *,row_number()over(partition by Number order by id)as rnk from
SequenceNumbers
)
select Distinct Number from cte where rnk >1;

[@kingsleyarisah6354]

SELECT Distinct Number
FROM
(SELECT *,COUNT(id) OVER (PARTITION BY NUMBER ORDER BY (SELECT NULL)) rn FROM SEEK) J
WHERE rn>=2

[@arjundev4908]

WITH CTE AS(SELECT *,
LEAD(Number)OVER(ORDER BY ID) AS NXT,
LAG(Number)OVER(ORDER BY ID) AS PREVIOUS
FROM SN)
SELECT DISTINCT NUMBER FROM CTE
WHERE NUMBER = NXT AND NXT = PREVIOUS;

[@HARSHRAJ-gp6ve]

with cte as(
select ID,Number,LEAD(Number)OVER() as x1,Lead(Number)OVER() as x2 FROM SequenceNumbers
)
select DISTINCT(Number) FROM cte where Number=x1 and x1=x2;

[@datasculptor2895]

X1 and X2 are always the same?

[@HARSHRAJ-gp6ve]

@@datasculptor2895 Sir sorry sir,
with cte as(
select ID,Number,LEAD(Number)OVER() as x1,Lead(Number,2)OVER() as x2 FROM SequenceNumbers
)
select DISTINCT(Number) FROM cte where Number=x1 and x1=x2;
plese check tis query sir

[@kingsleyarisah6354]

​@@HARSHRAJ-gp6ve This works. Also works if you swap out the Lead for Lag

[@datasculptor2895]

@@HARSHRAJ-gp6ve nice. If I want to change the question to get numbers which have 10 consecutive entries, how many changes should you make?

[@HARSHRAJ-gp6ve]

@@datasculptor2895 I will try to make this query more optimal

[@themightyvk]

With cte as(
select *, row_number() over(partition by number order by id) as rn,
id - row_number() over(partition by number order by id) as diff
from SequenceNumbers)
select distinct number from (
select *, count(diff) over( partition by number order by number) as grp from cte) a where grp >=3