Calculate consecutive days posting in Rails

Question

In a simple Ruby on Rails app I'm trying to calculate the number of consecutive days a User has posted. So for example, if I have posted each of the last 4 days, I'd like to have on my profile "Your current posting streak is 4 days, keep it up!" or something like that.

Should I be keeping track of the "streaks" in one of my models, or should I be calculating them elsewhere? Not sure where I should do it, or how to properly do so, so any suggestions would be wonderful.

I'm happy to include any code you'd find useful, just let me know.

Answer 1

I'm not sure if it's the best way, but here's one way to do it in SQL. First, take a look at the following query.

SELECT
  series_date,
  COUNT(posts.id) AS num_posts_on_date
FROM generate_series(
       '2014-12-01'::timestamp,
       '2014-12-17'::timestamp,
       '1 day'
     ) AS series_date
LEFT OUTER JOIN posts ON posts.created_at::date = series_date
GROUP BY series_date
ORDER BY series_date DESC;

We use generate_series to generate a range of dates starting on 2014-12-01 and ending 2014-12-17 (today). Then we do a LEFT OUTER JOIN with our posts table. This gives us one row for every day in the range, with the number of posts on that day in the num_posts_on_date column. The results looks like this (SQL Fiddle here):

 series_date                     | num_posts_on_date
---------------------------------+-------------------
 December, 17 2014 00:00:00+0000 |                 1
 December, 16 2014 00:00:00+0000 |                 1
 December, 15 2014 00:00:00+0000 |                 2
 December, 14 2014 00:00:00+0000 |                 1
 December, 13 2014 00:00:00+0000 |                 0
 December, 12 2014 00:00:00+0000 |                 0
 ...                             |               ...
 December, 01 2014 00:00:00+0000 |                 0

Now we know there's a post on every day from Dec. 14–17, so if today's Dec. 17 we know the current "streak" is 4 days. We could do some more SQL to get e.g. the longest streak, as described in this article, but since we're only interested in the length of the "current" streak, it'll just take a small change. All we have to do is change our query to get only the first date for which num_posts_on_date is 0 (SQL Fiddle):

SELECT series_date
FROM generate_series(
       '2014-12-01'::timestamp,
       '2014-12-17'::timestamp,
       '1 day'
     ) AS series_date
LEFT OUTER JOIN posts ON posts.created_at::date = series_date
GROUP BY series_date
HAVING COUNT(posts.id) = 0
ORDER BY series_date DESC
LIMIT 1;

And the result:

 series_date
---------------------------------
 December, 13 2014 00:00:00+0000

But since we actually want the number of days since the last day with no posts, we can do that in SQL too (SQL Fiddle):

SELECT ('2014-12-17'::date - series_date::date) AS days
FROM generate_series(
       '2014-12-01'::timestamp,
       '2014-12-17'::timestamp,
       '1 day'
     ) AS series_date
LEFT OUTER JOIN posts ON posts.created_at::date = series_date
GROUP BY series_date
HAVING COUNT(posts.id) = 0
ORDER BY series_date DESC
LIMIT 1;

Result:

 days
------
    4

There you go!

Now, how to apply it to our Rails code? Something like this:

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

Post.find_by_sql([ qry, { user_id: some_user.id } ]).first.days # => 4

As you can see, we added a condition to restrict results by user_id, and replaced our hard-coded dates with a query that gets the date of the user's first post (the sub-select inside the generate_series function) for the beginning of the range and CURRENT_DATE for the end of the range.

That last line is a little funny because find_by_sql will return an array of Post instances, so you then have to call days on the first one in the array on to get the value. Alternatively, you could do something like this:

sql = Post.send(:sanitize_sql, [ qry, { user_id: some_user.id } ])
result_value = Post.connection.select_value(sql)
streak_days = Integer(result_value) rescue nil # => 4

Within ActiveRecord it can be made a little cleaner:

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

  def self.user_streak_days(user_id)
    sql = sanitize_sql [ USER_STREAK_DAYS_SQL, { user_id: user_id } ]
    result_value = connection.select_value(sql)
    Integer(result_value) rescue nil
  end
end

class User < ActiveRecord::Base
  def post_streak_days
    Post.user_streak_days(self)
  end
end

# And then...
u = User.find(123)
u.post_streak_days # => 4

The above is untested, so it'll likely take some fiddling to make it work, but I hope it points you in the right direction at least.

Answer 2

I would create two columns in the user model. "streak_start", and "streak_end" which are timestamps.

Assuming posts belong to a user.

Post Model

after_create :update_streak  
def update_streak
    if self.user.streak_end > 24.hours.ago
        self.user.touch(:streak_end)
    else
        self.user.touch(:streak_start)
        self.user.touch(:streak_end)
    end
end

Personally I would write it like this:

def update_streak
    self.user.touch(:streak_start) unless self.user.streak_end > 24.hours.ago
    self.user.touch(:streak_end)
end

Then to determine a user's streak.

User Model

def streak
    # put this in whatever denominator you want
    self.streak_end > 24.hours.ago ? (self.streak_end - self.streak_start).to_i : 0
end

Answer 3

Another nice solution can be found here. With this code you can see consecutive days also for yesterday, even if your users at that moment doesn't have any posts today. It will motivate users tо continue their streak.

def get_last_user_posts_steak
    qry = <<-SQL
        WITH RECURSIVE CTE(created_at)
        AS
        (
           SELECT * FROM 
           (
              SELECT created_at FROM posts WHERE posts.user_id = :user_id AND ( created_at::Date = current_date 
              OR created_at::Date = current_date - INTERVAL '1 day' )
              ORDER BY created_at DESC
              LIMIT 1
           ) tab
           UNION ALL

           SELECT a.created_at FROM posts a
           INNER JOIN CTE c
           ON a.created_at::Date  = c.created_at::Date  - INTERVAL '1 day' AND a.user_id = :user_id
           GROUP BY a.created_at
        ) 
        SELECT COUNT(*) FROM CTE;
    SQL

    sql = sanitize_sql [ qry, { user_id: user.id } ]
    result_value = connection.select_value(sql)
    return Integer(result_value) rescue 0
end

Result = Post.get_last_user_posts_steak(current_user)