0
0 Comments

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
    ,row_number() OVER (
        ORDER BY MIN(Date)
        ) AS event_sequence_number
    ,MIN(Date) AS time_started
    ,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 Tababitosan 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
Anonymous Asked question May 14, 2021