Get dates interval based on table of states

I have a table that contains the states of whether a product was in promotion or not, along with the day of the state. The table looks like this:

day	        promotion_product_id   prev_order_promotion_product_id
2023-02-22  1251589	               1251589
2023-02-23  1251589	               1251589
2023-03-03  1251589	               1251589
2023-03-07  1251589	               1251589
2023-03-08  null                   1251589
2023-03-09  1251589	               null
2023-03-10  1251589	               1251589

What I want to get are the periods in which a certain product_id was in promotion. So for this case, the desired output would be:

promotion_product_id  start_promotion_at  end_promotion_at
1251589               2023-02-22          2023-03-07
1251589               2023-03-09          2023-03-10

I have been trying to use some windows function (max, min etc) but I am not even close to get the solution.

You can use window function to generate promotion_bucket_number and group by promotion_bucket_number

Here is the query:

with data_with_promotion_bucket AS 
(select
   dt,
   promotion_product_id,
   prev_order_promotion_product_id,
   sum(case 
       when promotion_product_id is not null 
       and promotion_product_id <> coalesce(prev_order_promotion_product_id,-1) 
       then 1 
       else 0 
       end
   ) over (order by dt) as promotion_bucket_number
from
   data_table
)
select
    max(promotion_product_id) as promotion_product_id,
    min(dt) as start_promotion_at,
    max(dt) as end_promotion_at  
from
    data_with_promotion_bucket
where
    promotion_product_id is not null
group by
    promotion_bucket_number

You can use query as below. You must use your table instead of temp table.

SELECT promotion_product_id, 
       MIN(day), 
       MAX(day)
FROM
(
    SELECT *, 
           SUM(IIF(promotion_product_id IS NULL, 1, 0)) OVER( partition by isnull(promotion_product_id,prev_promotion_product_id)
           ORDER BY day) grp
    FROM #temp
) AS t
WHERE promotion_product_id IS NOT NULL
GROUP BY promotion_product_id, 
         grp;