Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table some_sample_data 
  ( product integer,
    _date date,
    in_promo varchar(100)
  )


CREATE TABLE


insert into some_sample_data values (1, '2023-10-01', 'false');
insert into some_sample_data values (1, '2023-10-02', 'false');
insert into some_sample_data values (1, '2023-10-03', 'true');
insert into some_sample_data values (1, '2023-10-04', 'true');
insert into some_sample_data values (1, '2023-10-05', 'true');
insert into some_sample_data values (1, '2023-10-06', 'false');
insert into some_sample_data values (1, '2023-10-07', 'false');
insert into some_sample_data values (1, '2023-10-08', 'true');
insert into some_sample_data values (1, '2023-10-09', 'true');
insert into some_sample_data values (1, '2023-10-10', 'false');
insert into some_sample_data values (2, '2023-10-01', 'true');
insert into some_sample_data values (2, '2023-10-02', 'false');
insert into some_sample_data values (2, '2023-10-03', 'false');
insert into some_sample_data values (2, '2023-10-04', 'true');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


with sequenced_result as (
      SELECT 
        *,
        row_number() over ( partition by product order by cast(_date as date) asc) rn,
        lag(in_promo) OVER(PARTITION BY product ORDER BY cast(_date as date) asc) as prev_value,
        case when in_promo = 'true'
              and coalesce(lag(in_promo) OVER(PARTITION BY product ORDER BY cast(_date as date) asc),'false') = 'false'
             then 1
             else 0
          end originated_as_or_change_to_true
    FROM some_sample_data
  ),
  earlier_snapshots as (
  select t1.*,
         max(t2.rn) as rn_of_last_change
    from sequenced_result t1
    left
    join sequenced_result t2
      on t1.product = t2.product
     and t1._date >= t2._date
     and t2.originated_as_or_change_to_true = 1
   group
      by t1.product,
         t1._date,
         t1.in_promo,
         t1.rn,
         t1.prev_value,
         t1.originated_as_or_change_to_true
  )
  select product,
         _date,
         in_promo,
         rn - rn_of_last_change + 1 as days_since_last_promo
    from earlier_snapshots
   order 
      by product, 
         _date


product
_date
in_promo
days_since_last_promo


1
2023-10-01
false
null

1
2023-10-02
false
null

1
2023-10-03
true
1

1
2023-10-04
true
2

1
2023-10-05
true
3

1
2023-10-06
false
4

1
2023-10-07
false
5

1
2023-10-08
true
1

1
2023-10-09
true
2

1
2023-10-10
false
3

2
2023-10-01
true
1

2
2023-10-02
false
2

2
2023-10-03
false
3

2
2023-10-04
true
1



SELECT 14

product	_date	in_promo	days_since_last_promo
1	2023-10-01	false	null
1	2023-10-02	false	null
1	2023-10-03	true	1
1	2023-10-04	true	2
1	2023-10-05	true	3
1	2023-10-06	false	4
1	2023-10-07	false	5
1	2023-10-08	true	1
1	2023-10-09	true	2
1	2023-10-10	false	3
2	2023-10-01	true	1
2	2023-10-02	false	2
2	2023-10-03	false	3
2	2023-10-04	true	1