islands_of_data

-- решение для задачи - https://qna.habr.com/q/1354402
WITH DATA AS (
    -- считаем разницу между текущим и предыдущим периодом:
    SELECT store_id, period, sales_volume, lag
      , period - lag AS cnt
    FROM promo
)
, temp AS (
    -- задаём группы данных (острова данных):
    SELECT store_id, period, sales_volume
       , SUM(CASE WHEN cnt > 1 THEN 1 ELSE 0 END) OVER (partition BY store_id ORDER BY period) AS grp_id
    FROM DATA
)

SELECT store_id, period, sales_volume
    , dense_rank() OVER(partition BY store_id ORDER BY grp_id) AS promo_id
    , ROW_NUMBER() OVER(partition BY store_id, grp_id ORDER BY period) - 1 AS promo_cw
FROM temp