Increment column value on certain condition in SQL query on Postgresql

Question

I want aggregate my walks with animals by weeks groupping my rows in 1 group if break between weeks was greater than 2 weeks.

I have my table:

Create table test.walk (animal text, week integer)

with row for each walk i want to group:

insert into test.walk values ('DOG', 2)
insert into test.walk values ('DOG', 3)
insert into test.walk values ('DOG', 4)
insert into test.walk values ('CAT', 1)
insert into test.walk values ('CAT', 1)
insert into test.walk values ('CAT', 11)
insert into test.walk values ('CAT', 12)
insert into test.walk values ('CAT', 13)

I struggle with dense_rank() and lag() window functions, but have no luck to get additional column where I should get 3 distinct values as a result.

Here is my desired output:

What combination of window functions should I use to get two WALK_NO for CAT? (becase after week 1 cat waited for me longer that 2 weeks)

Answer 1

Use lag() and cumulative sum:

with query as (
select w.*,
       sum(case when week < prev_week + 2 then 0 else 1 end) over (partition by animal order by week) as grp
from (select w.*,
             lag(week) over (partition by animal order by week) as prev_week
      from test.walk w
     ) w
)
select
    animal, week, grp,
    dense_rank() over (order by animal, grp) as grp2
from query

Note: This will restart the counting over for each animal -- which seems to be the actual intention of what you want to do. The definition of the problem is a bit tricky if you want the groups separated by animal, but to be incremental. One method is:

select w.*,
       sum(case when prev_week = week then 0 else 1 end) over (order by min_week, animal, week) as grp
from (select w.*,
             lag(week) over (partition by animal order by week) as prev_week,
             min(week) over (partition by animal) as min_week
      from test.walk w
     ) w;