Postgres Consecutive Days, gaps and islands, Tabibitosan

Question

SQL FIDDLE

I have the following database table:

date	name
2014-08-10	bob
2014-08-10	sue
2014-08-11	bob
2014-08-11	mike
2014-08-12	bob
2014-08-12	mike
2014-08-05	bob
2014-08-06	bob

SELECT t.Name,COUNT(*) as frequency
FROM (
    SELECT Name,Date,
            row_number() OVER (
            ORDER BY Date
            ) - row_number() OVER (
            PARTITION BY Name ORDER BY Date
            ) + 1 seq
    FROM orders
    ) t
GROUP BY Name,seq;

Tried running the Tabibitosan method of finding gaps and islands produces the below table which is incorrect. The name "mike" should actually have a count of 2 since the 11th and 12th days are consecutive. How do I fix this?

name	frequency
mike	1
bob	3
bob	2
mike	1
sue	1

Correct expected output below:

name	frequency
bob	3
bob	2
mike	2
sue	1

Answer 1

You are using the wrong logic. Basically, you want dates that are sequential, so you want to subtract the sequence from the date:

SELECT t.Name, COUNT(*) as frequency
FROM (SELECT o.*,
             row_number() OVER (PARTITION BY Name ORDER BY Date) as seqnum
      FROM orders o
     ) t
GROUP BY Name, date - seqnum * interval '1 day';

Here is a db<>fiddle.