Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


CREATE TABLE my_table (
  name text,
  day int,
  healthy boolean
);

INSERT INTO my_table
VALUES
  ('Jon', '1', 'No'),
  ('Jon', '2', 'Yes'),
  ('Jon', '3', 'Yes'),
  ('Jon', '4', 'Yes'),
  ('Jon', '5', 'No'),
  ('Mary', '1', 'Yes'),
  ('Mary', '2', 'No'),
  ('Mary', '3', 'Yes'),
  ('Mary', '4', 'No'),
  ('Mary', '5', 'Yes');

CREATE TABLE



INSERT 0 10


select name, day, 
       sum(healthy::int) 
         over (partition by name 
                   order by day
            rows between current row
                     and unbounded following) as num_subsequent_health_days
  from my_table;


name
day
num_subsequent_health_days


Jon
1
3

Jon
2
3

Jon
3
2

Jon
4
1

Jon
5
0

Mary
1
3

Mary
2
2

Mary
3
2

Mary
4
1

Mary
5
1



SELECT 10