Given a MySQL table with timestamp
and user
columns, I'd like to be able to count how many consecutive days (must end with today) records exist for a given user.
All of the examples on stackoverflow/google involve finding previous streaks, or counting total streaks, but I need to know about their current streak;
I can use this to find all days of which there are records for the day prior:
select date(start_of_votes.date_created)
from votes start_of_votes
left join votes previous_day
on start_of_votes.username = previous_day.username
and date(start_of_votes.date_created) - interval 1 day = date(previous_day.date_created)
where previous_day.id is not null
and start_of_votes.username = "bob"
group by date(start_of_votes.date_created) desc
But I need to only count ranges that include a record for today.
Per request, some sample data:
bob 2014-08-10 00:35:22
sue 2014-08-10 00:35:22
bob 2014-08-11 00:35:22
mike 2014-08-11 00:35:22
bob 2014-08-12 00:35:22
mike 2014-08-12 00:35:22
Today being Aug 12 2014:
bob
has a streak of 3 days
sue
has no current streak
mike
has a streak of 2 days
This data is per-user, so I'll run a query for bob
and get 3
as the result. I don't need a result broken down by user.