PostgreSQL – How to Count Three Consecutive Records with Same Values in a Column

postgresql

I have a table attendance as follows:

create table attendance (id int, name varchar(30), status varchar(10), date date)

This table has the following records: 

insert into attendance values (1,'John','absent','2016-03-01');
insert into attendance values (1,'John','absent','2016-03-02');
insert into attendance values (1,'John','absent','2016-03-03');
insert into attendance values (2,'Sam','present','2016-03-04');
insert into attendance values (3,'Sam','absent','2016-03-05');
insert into attendance values (1,'John','absent','2016-03-06');
insert into attendance values (1,'John','absent','2016-03-07');
insert into attendance values (1,'John','absent','2016-03-08');
insert into attendance values (1,'John','present','2016-03-09');
insert into attendance values (1,'John','absent','2016-03-10');
insert into attendance values (1,'John','absent','2016-03-11');
insert into attendance values (1,'John','present','2016-03-12'); 
insert into attendance values (1,'John','absent','2016-03-13');

Now I want to count that how many times a person has three consecutive absent records.
The result should be as follows: 

id name count
 1  John   2

because John is absent for three consecutive days two times.

If they have 6 absents on 6 consecutive dates, then it should count 2 i.e (3+3).

It should count 1 for 3 consecutive absents on 3 consecutive dates i.e if John is absent for 1,2 and 3 march, it should count 1. but if John is absent for 1,2 and 4 march then it should not count 1.
4 or 5 absents should be counted as 1.

If there is no entry for a date, It is considered 'Present'.

Any help is appreciated.

Best Answer

Another solution. Recursive part creates groups of rows of status "absent". The solution uses recursive CTEs and window functions.

WITH RECURSIVE a(id, name, date, n)  as (
    SELECT id, name, q.date, 1 as n
    FROM (
        SELECT id, name, date,
            date-lag(date) OVER (PARTITION BY name ORDER BY date) as lag
        FROM  attendance
        WHERE status='absent'
    ) q
    WHERE lag >1 or lag is null
    UNION
    SELECT a.id, a.name, a.date, a.n + 1
    FROM a
    JOIN attendance at ON (
           a.id = at.id and at.name = a.name and at.date = a.date + n)
    WHERE at.status='absent'
)
SELECT id, name, sum(long_absences) FROM (
    SELECT id, name, count(*)/3 as long_absences
    FROM a
    GROUP BY id, name, date having count(*) >=3
    ) as absences
GROUP BY id, name;
Related Question