UST Global SQL Interview Question 2024 | Identifying Gaps in a Sequence

[@akashdongre7040]

with final as
(select
coalesce(order_id - lag(order_id) over (partition by id order by id),0) as diff ,
row_number() over (partition by id order by id) as row_num
from
input_table)
select
row_num as Order_id
from
final
where diff=2

[@skilltechath0n]

@akashdongre7040 Great! Keep practicing :)

[@ishanshubham8355]

WITH RECURSIVE CTE AS
(
SELECT MIN(ORDER_ID) AS cnt
FROM orders
UNION
SELECT cnt+1
FROM cte
WHERE cnt < (SELECT max(order_id) FROM orders)
)
SELECT cnt AS order_id
FROM cte
WHERE cnt NOT IN (SELECT order_id FROM orders)

[@skilltechath0n]

@ishanshubham8355 Great! Keep practicing :)