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.