CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
mikker
mikker

Reputation: 831

Best way to calculate a non-broken streak of days

In my app a User has_many Gestures. What is a good way to calculate how many subsequent days the User has done Gestures?

Right now I'm doing it like below, and it works as I want it to. But clearly it doesn't scale.

class User < ActiveRecord::Base

  # ...

  def calculate_current_streak
    return 0 unless yesterday = gestures.done_on_day(Date.today-1)
    i = 0
    while gesture = self.gestures.done_on_day(Date.today - (i+1).days).first
      i += 1
    end
    i += 1 if gestures.done_on_day(Date.today).first
    i
  end

end

Thanks! Special points to the one who can work in a way to only track business days too :)

Upvotes: 3

Views: 690

Answers (1)

Mladen Jablanović
Mladen Jablanović

Reputation: 44090

Think about it this way:

The length of the streak is equivalent to the number of (business) days passed since the last (business) day the user didn't use gestures at all, right?

So the solution boils down to calculating most recent day that the user didn't make a gesture.

In order to do this most easily, there's a trick that lots of DB admins use: they create DB table with all dates (say, all the dates from year 2000 to year 2100). The table needs to have only date field, but you can throw in a Boolean field to mark non-working days, such as weekends and holidays. Such table can be handy in lots of queries, and you only have to create and fill it once. (Here's a more detailed article on such tables, written for MS SQL, but insightful anyway.)

Having this table (let's call it dates), you can calculate your pre-streak date using something like (pseudo-SQL):

SELECT d.date
FROM dates d
LEFT JOIN gestures g ON d.date = g.date AND g.user_id = <put_user_id_here>
WHERE d.date < TODAY() AND g.date IS NULL AND d.work_day = TRUE
ORDER BY d.date DESC
LIMIT 1

Upvotes: 4

Related Questions