Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table have (
    n int,
    x int
) ;
insert into have (n,x)
values (1,0),(2,1),(3,2),(4,3),(5,4),(6,2),(7,1),(8,0),(9,1),(10,0),(11,0),(12,0),(13,0),(14,1),(15,2),(16,3),(17,4)
returning *;

CREATE TABLE




n
x


1
0

2
1

3
2

4
3

5
4

6
2

7
1

8
0

9
1

10
0

11
0

12
0

13
0

14
1

15
2

16
3

17
4



INSERT 0 17


--Both start at zero. If at any point in time x > 2 then def = 1. 
--Now if def = 1 then in order for it to become zero again, 
--x has to equal zero for 3 consecutive periods otherwise it remains equal to 1.
--explain analyze verbose
with cte as (
    select *
      ,(x > 2) exceeded_2
      ,(every(x=0)over w1) as should_switch
    from have
    window w1 as (order by n rows 2 preceding) )
,cte2 as (
    select *,sum(should_switch::int)over(order by n) def_on_period 
    from cte
)
select n,x,(bool_or(exceeded_2) over w2)::int as def
from cte2
window w2 as (partition by def_on_period 
              order by n);


n
x
def


1
0
0

2
1
0

3
2
0

4
3
1

5
4
1

6
2
1

7
1
1

8
0
1

9
1
1

10
0
1

11
0
1

12
0
0

13
0
0

14
1
0

15
2
0

16
3
1

17
4
1



SELECT 17


create or replace function calc_dpd()
returns table (
    n int,
    x int,
    def int )
language plpgsql as $f$
declare rec record;
        prev_x  int:=0;
        prev_x2 int:=0;
begin
    def = 0;
    for rec in (select * from have order by n) loop 
        if rec.x > 2 then 
            def := 1;
        elsif 0=all(array[rec.x, prev_x, prev_x2]) then
            def := 0;
        end if;
        return query select rec.n, rec.x, def;
        prev_x2 := prev_x;
        prev_x  := rec.x;
    end loop;
end $f$;

select * from calc_dpd();

CREATE FUNCTION




n
x
def


1
0
0

2
1
0

3
2
0

4
3
1

5
4
1

6
2
1

7
1
1

8
0
1

9
1
1

10
0
1

11
0
1

12
0
0

13
0
0

14
1
0

15
2
0

16
3
1

17
4
1



SELECT 17