Rails - How to Query for Consecutive Days

Question

Let's say I have the following records:

Table: sessions
+----+-------------------------+-------------------------+---------+----------+   
| id | created_at              | updated_at              | user_id | group_id |
+----+-------------------------+-------------------------+---------+----------+
| 1  | 2014-06-08 20:37:03 UTC | 2014-06-08 20:37:03 UTC | 1       | 3        | 
| 31 | 2014-06-09 17:23:33 UTC | 2014-06-09 17:23:33 UTC | 1       | 4        |
| 32 | 2014-06-10 22:26:58 UTC | 2014-06-10 22:27:08 UTC | 1       | 2        |
| 33 | 2014-06-11 22:56:06 UTC | 2014-06-11 22:56:18 UTC | 1       | 2        |
| 35 | 2014-06-16 17:25:55 UTC | 2014-06-16 17:26:06 UTC | 1       | 2        | 
| 36 | 2014-06-17 17:26:34 UTC | 2014-06-17 17:26:47 UTC | 1       | 2        |
| 37 | 2014-06-18 17:46:51 UTC | 2014-06-18 17:46:51 UTC | 1       | 2        |
| 38 | 2014-06-19 17:47:47 UTC | 2014-06-19 17:49:00 UTC | 1       | 1        |
+----+-------------------------+-------------------------+---------+----------+

How would I query the database to get a user's longest "streak" of consecutive days.

So, based on records 35-38 above, there are consecutive records between 7/16 - 7/19, which would return 4.

I'm using PostgreSQL with Rails 4.

Answer 1

This first query should show you the "start date" of each "consecutive group" (each string of consecutive days) and the number of consecutive days in each group.

I did this for illustration just so you can see how it works - http://sqlfiddle.com/#!15/ad3e1/22/0

with sub as(
select user_id, dy, prev_dy, grp, case when lead(grp,1) over (order by dy) = grp+1 or lag(grp,1) over (order by dy) = grp-1 then 'X' else null end as gid
  from (select user_id,
               dy,
               prev_dy,
               row_number() over(partition by dy - prev_dy <> 1 order by dy) as grp
          from (select user_id, dy, lag(dy, 1) over(order by dy) as prev_dy
                  from (select distinct user_id,
                                        cast(created_at as date) as dy
                          from sessions
                         where user_id = 1
                         order by dy) x
                 order by dy) x
         order by dy) x
 order by dy)
select x.dy, count(*)
from sub x cross join sub y
where x.gid is null
and y.dy >= x.dy
and (y.dy < (select min(z.dy) from sub z where z.gid is null and z.dy > x.dy)
or not exists (select 1 from sub z where z.gid is null and z.dy > x.dy))
group by x.dy

To just get the result of 4 (there are actually two strings of 4 days so you have a tie in this case), you can run the below which just grabs the highest number of "consecs" from my query above (the row representing the group with the highest # of consecutive days) --

with sub as(
select user_id, dy, prev_dy, grp, case when lead(grp,1) over (order by dy) = grp+1 or lag(grp,1) over (order by dy) = grp-1 then 'X' else null end as gid
  from (select user_id,
               dy,
               prev_dy,
               row_number() over(partition by dy - prev_dy <> 1 order by dy) as grp
          from (select user_id, dy, lag(dy, 1) over(order by dy) as prev_dy
                  from (select distinct user_id,
                                        cast(created_at as date) as dy
                          from sessions
                         where user_id = 1
                         order by dy) x
                 order by dy) x
         order by dy) x
 order by dy)
select max(consecs)
from(
select x.dy, count(*) as consecs
from sub x cross join sub y
where x.gid is null
and y.dy >= x.dy
and (y.dy < (select min(z.dy) from sub z where z.gid is null and z.dy > x.dy)
or not exists (select 1 from sub z where z.gid is null and z.dy > x.dy))
group by x.dy) x

http://sqlfiddle.com/#!15/ad3e1/24/0