PostgreSQL: find number of consecutive days up until now

Question

Given a bunch of records (which represent checkins in my app) with a timestamp field, what would be a good way to determine the current streak of consecutive checkins?

In other words, with the checkins sorted by checkin time descending, how many records are there until a user missed a day?

Currently I'm using this technique:

SELECT distinct(uca.created_at::date) as created_at
    FROM user_challenge_activities as uca INNER JOIN user_challenges as uc
    ON user_challenge_id = uc.ID WHERE uc.user_id = #{user.id}
    order by (uca.created_at::date) DESC;

...where I cast the checkin timestamps to a date (to end up with e.g. 2012-03-20), then in code, go through the records and increment a counter until the date between the record and the next record is greater than 1 day.

However, this approach seems clumsy to me, and it seems like the sort of thing that Postgres would excel at.

So is there in fact a better way to accomplish this?

Define "current" in "current streak of consecutive check-ins". You mean the streak including today? Or just the latest streak? — Erwin Brandstetter, Mar 20 '12 at 21:18
Ah yes, current streak of consecutive check-ins including today. Essentially, the number of consecutive days including today that you've checked in — yalestar, Mar 20 '12 at 21:24

Clodoaldo Neto · Accepted Answer · 2012-03-20T22:27:59.543

5

with t as (
    SELECT distinct(uca.created_at::date) as created_at
    FROM user_challenge_activities as uca 
    INNER JOIN user_challenges as uc ON user_challenge_id = uc.ID 
    WHERE uc.user_id = #{user.id}
    )
select count(*)
from t
where t.create_at > (
    select d.d
    from generate_series('2010-01-01'::date, CURRENT_DATE, '1 day') d(d)
    left outer join t on t.created_at = d.d::date
    where t.created_at is null
    order by d.d desc
    limit 1
)

edited Mar 20 '12 at 22:27

answered Mar 20 '12 at 20:49

Clodoaldo Neto

118,695
26
233
260

Now that is a little slice of awesome! One question though: what is the significance of the 2010-01-01? – yalestar Mar 20 '12 at 21:11
1

I think the CTE needs brackets around it: `with t as ( ... )` – Mar 20 '12 at 22:11
What do you mean by "d.d" ? – Parth Shah Dec 07 '21 at 10:13

Lukas Eklund · Answer 2 · 2012-03-20T21:54:15.360

let's try this again. will only generate series for the necessary range:

SELECT count(distinct(uca.created_at::date)) FROM user_challenge_activities as uca
JOIN 
  (SELECT generate_series(max(series_date), 
                         (select max(user_challenge_activities.created_at) 
                          FROM user_challenge_activities), '1 day') as datez 
   FROM 
     (SELECT generate_series(min(user_challenge_activities.created_at::date),
                             max(user_challenge_activities.created_at), '1 day')::date
             as series_date 
      FROM user_challenge_activities) x
   LEFT JOIN user_challenge_activities 
     ON (user_challenge_activities.created_at::date = x.series_date)
   WHERE created_at IS NULL) d ON d.datez = uca.created_at::date
 INNER JOIN user_challenges as uc ON user_challenge_id = uc.ID 
 WHERE uc.user_id = #{user.id};

PostgreSQL: find number of consecutive days up until now

2 Answers2

Linked