I am trying to count the number of consecutive weeks an employee went to work. So I have this table that has whether jon or andy went to work on certain weeks (I have all week of the year).

I am trying on Postgresql

What I would like know the number of times each person went consecutively to work x number of weeks.

So the way the below is read is that Andy went twice two consecutive weeks.

I feel like I am close. On python I could use a for loop probably, but on Postgresql I am a bit lost.

Thanks!

CodePudding user response：

We group each amount of consecutive weeks worked per person and then group by the result and the person.

select    person
         ,consecutive_weeks
         ,count(*)/consecutive_weeks as times
from      (
          select    person
                   ,sum(case when "went to work?" = 1 then 1 end) over(partition by person, grp) as consecutive_weeks
          from     (
                   select  *
                           ,count(mrk) over(partition by person order by week) as grp
                   from    (
                           select  *
                                   ,case when "went to work?" <> lag("went to work?") over(partition by person order by week) then 1 end as mrk
                           from    t
                           ) t
                  ) t
         ) t
where    consecutive_weeks is not null
group by person, consecutive_weeks
order by person

Fiddle

CodePudding user response：

You can find groups of weeks where a person was present, assigning a running id to each row of the group, and then apply a count on the results, performing a group by on the id:

with cte as (
   select t3.person, t3.k, count(*) c from 
      (select t.*, (select sum((t1.person = t.person and t1.week <= t.week and t1.at_work = 0)::int) k from tbl t1) 
       from tbl t) t3 
   where t3.at_work != 0 group by t3.person, t3.k
)
select c.person, c.c, count(*) c1 from cte c group by c.person, c.c order by c1

See fiddle.