Mysql – How to calculate streaks of consecutive dates for a user

MySQL

Assume a results table like so:

date|user_id

A user can have one or more result per day..

I need to calculate a) users who have entered a result in 30 or more consecutive days b) preferably the longest current streak for a specific user.

The results table is large (2 million results) with approx 50k users.

Alternatively, if this is too difficult, any suggestions of a different way of working this out?

(e.g. create a 'streak' table with user_id, streak number, start date etc and then update that on insert into results, but you'd still need to know if somebody entered a result whether that was part of a streak etc)

Best Answer

To keep track of longest streak and when it occurred.

Table columns needed:

current_streak          int
longest_streak          int
longest_streak_end_date datetime

Pseudocode:

if user has logged in today already
    return
if user logged in yesterday
    add 1 to current_streak
    if current_streak > longest_streak
        set longest_streak = current_streak
        set longest_streak_end_date = today
else
    set current_streak = 1
Related Question