https://www.youtube.com/watch?v=UCywRDY4F68

-- Create the orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

-- Insert Data
INSERT INTO orders (order_id, order_date) 
VALUES
    (1,  '2024-07-01'),
    (2,  '2024-07-02'),
    (3,  '2024-07-03'),
    (5,  '2024-07-05'),
    (6,  '2024-07-06'),
    (7,  '2024-07-07'),
    (9,  '2024-07-09'),
    (10, '2024-07-10'),
    (11, '2024-07-11'),
    (13, '2024-07-13'),
    (14, '2024-07-14'),
    (15, '2024-07-15'),
    (16, '2024-07-16'),
    (18, '2024-07-18'),
    (19, '2024-07-19'),
    (20, '2024-07-20');

WITH
ROW_NUMBER()

-- Approach 1
with cte as (
select * 
	, order_id - row_number() OVER (order by order_id) as rn
from orders
)

select max(order_id)+1 as missing
FROM cte
where rn<(select max(rn) from cte)
group by rn;

WITH RECURSIVE

-- Approach 2
with recursive cte as 
	(
    select 1 as counter from orders
    union 
    select counter+1
    from cte
    where counter<(select max(order_id) from orders)
    )
select counter as Missing from cte
c left join orders o on c.counter = o.order_id
where order_id is null