Find the longest days in row for users in Rails with postgres

Question

I'm trying to determine the most consecutive days for history. I tried to use the solution from here, but for some reason I receive nil.

I know that there are other solutions with SQL like here, but SQL is not my strong suit.

I need to find what was the longest consecutive streak of histories for a user without interruption (any number of days with no history will break the streak).

Table example:

Class History
user_id: 1, created_at: '2018-01-02' # streak 1 #=> start
user_id: 1, created_at: '2018-01-03' # streak 2
user_id: 1, created_at: '2018-01-04' # streak 3
user_id: 1, created_at: '2018-01-04' # streak 3 #=> end 
user_id: 1, created_at: '2018-01-06' # streak 1 #=> start and end
user_id: 1, created_at: '2018-01-08' # streak 1 #=> start
user_id: 1, created_at: '2018-01-09' # streak 2 #=> end
user_id: 1, created_at: '2018-01-11' # streak 1 #=> start and end
user_id: 1, created_at: '2018-01-17' # streak 1 #=> start
user_id: 1, created_at: '2018-01-18' # streak 2
user_id: 1, created_at: '2018-01-19' # streak 2
user_id: 1, created_at: '2018-01-20' # streak 3
user_id: 1, created_at: '2018-01-21' # streak 4
user_id: 1, created_at: '2018-01-22' # streak 5 # end # the longest steak 5 I need to receive 5
user_id: 1, created_at: '2018-01-25' # ...

I need to receive the number 5 as its the longest streak (5 days in a row). It would also be nice to find the start day and end day of the longest streak, but not a must.

Here is what I tried:

qry  = <<-SQL
  SELECT (CURRENT_DATE - series_date::date) AS days
  FROM generate_series(
         ( SELECT created_at::date FROM histories
           WHERE histories.user_id = :user_id
           ORDER BY created_at
           ASC LIMIT 1
         ),
         CURRENT_DATE,
         '1 day'
       ) AS series_date
  LEFT OUTER JOIN histories ON histories.user_id = :user_id AND
                           histories.created_at::date = series_date
  GROUP BY series_date
  HAVING COUNT(histories.id) = 0
  ORDER BY series_date DESC
  LIMIT 1
SQL

History.find_by_sql([ qry, { user_id: current_user.id } ])

But the response is:

# => [#<History id: nil>]

Maybe it's because I am using Postgres, but I ran it through the converter, so its looks ok. Or not?

I would really appreciate any working solution for this problem.

It's pretty hard to conceptualize what you are doing without a schema. — moveson, Feb 01 '18 at 17:58
I think I see what you're doing, but if you could put an example schema with some data up on [SQLFiddle](http://sqlfiddle.com/) that would be helpful to confirm a suggested query works. — Abe Voelker, Feb 01 '18 at 18:17
@AbeVoelker here is the [example](http://qlfiddle.com/#!15/501bc/1) the needed result is 5, (its just updated version [sqlfiddle](http://sqlfiddle.com/#!15/550ad/3) of from the [answer](http://stackoverflow.com/a/27537072/3002847)) — GEkk, Feb 01 '18 at 18:50
@GEkk Earlier I posted the code directly from the referenced blog post, but it was incompatible with Postgres and had another bug or two. Please see the updated post below, which I have tested in Postgres. — moveson, Feb 02 '18 at 03:05

moveson · Accepted Answer · 2018-02-02T03:02:39.830

This problem was analyzed and solved (brilliantly, I think) in this blog post.

With a bit of adaptation it should work for your problem. I've substituted your tables and column names and modified the date difference line to work in Postgres:

# Based on https://blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/

WITH

  dates(date) AS (
    SELECT DISTINCT CAST(created_at AS DATE)
    FROM histories
    WHERE histories.user_id = :user_id
  ),

  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY date) AS rn,
      date - (ROW_NUMBER() OVER (ORDER BY date) * interval '1' day) AS grp,
      date
    FROM dates
  )

SELECT
  COUNT(*) AS consecutive_dates,
  MIN(date) AS min_date,
  MAX(date) AS max_date
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC

I would highly recommend that you read the full blog post to understand the workings of the code.

Find the longest days in row for users in Rails with postgres

1 Answers1