Generate series of integer based on the condition of another column in Postgres

Question

I have the following datatable

create table test.my_table
(
  date                    date,
  daily_cumulative_precip real
);


INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-11', 0.508);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-12', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-13', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-14', 2.032);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-15', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-16', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-17', 21.842);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-18', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-19', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-20', 0);

I would like to create and assign values in a new column named 'delta' based on daily_cumulative_precip. I would like to have delta = 0 when daily_cumulative_precip > 0, delta = 1 when daily_cumulative_precip = 0 on that day and the previous day, delta = 2 when daily_cumulative_precip = 0 on that day and the previous 1 days, and delta = 3 when daily_cumulative_precip = 0 on that day and the previous 2 days. For this specific datatable, delta should be

0, 1, 2, 0, 1, 2, 0, 1, 2, 3

I have the following but it does not produce the desired result

SELECT *,
      CASE
        WHEN daily_cumulative_precip > 0 THEN 0
        --ELSE date - first_value(date) OVER (ORDER BY date)
          ELSE date - lag(date) OVER (ORDER BY date)
          END AS delta
FROM "test".my_table
ORDER BY date;

I highly appreciate your help.

Answer 1

For your particular data, the following works:

select t.*,
       (date - max(date) filter (where daily_cumulative_precip > 0) over (order by date))
from my_table t
order by date;

This gets the most recent date where the value is greater than 0.

This assumes that the first day has a value greater than 0. If this is not always the case, then:

select t.*,
       (date -
        coalesce(max(date) filter (where daily_cumulative_precip > 0) over (order by date),
                 min(date) over (order by date)
                )
       ) as seqnum
from my_table t
order by date;

Here is a db<>fiddle.