CODEWITHSUNDEEP
phpmysqldatabasemysql-5.7
Tokeeen.com
Tokeeen.com

Reputation: 738

Compute consecutive day streaks with MySQL

I have the following table : 

Date       | isDone
--------------------
2018-10-01 | 1
2018-10-02 | 1
2018-10-03 | 1
2018-10-04 | 1 
2018-10-10 | 0
2018-10-15 | 1
2018-10-16 | 0
2018-10-18 | 1
2018-10-19 | 1
2018-10-20 | 1

There can be only one row by day, with 2 possible values 0 or 1. It is not mandatory for a day to have a row. If there isn't a row for a day the value is considered as 0.

I want to compute the "current streak" of done tasks (the isDone column) from the current date. Assume we are the 2018-10-20, the result would be 3. For now, I don't want to compute the biggest streak of the month which would be in this case 4 (from 2018-10-01 to 2018-10-04). I am wondering what is the best option to compute this, directly with SQL (MySQL 5.7) or getting raw data by day and computing the streak with PHP?

Upvotes: 2

Views: 980

Answers (1)

Nick
Nick

Reputation: 147206

You can use MySQL variables to compute the streak. Basically you need to increment the streak on consecutive days when isDone is 1, and reset it when isDone is 0 or the dates are not consecutive. This query will produce the streak values for each day:

SELECT s.Date,
       @streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
       @last_date := Date AS last_date
FROM status s
JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
ORDER BY s.Date

Output:

Date        streak  last_date
2018-10-01  1       2018-10-01
2018-10-02  2       2018-10-02
2018-10-03  3       2018-10-03
2018-10-04  4       2018-10-04
2018-10-10  1       2018-10-10
2018-10-15  1       2018-10-15
2018-10-16  1       2018-10-16
2018-10-18  1       2018-10-18
2018-10-19  2       2018-10-19
2018-10-20  3       2018-10-20

Then you can use that as a subquery to determine the streak as of a given day e.g.

SELECT Date, streak
FROM (SELECT s.Date,
             @streak := IF(Date = @last_date + INTERVAL 1 DAY AND isDone = 1, @streak+1, 1) AS streak,
             @last_date := Date AS last_date
      FROM status s
      JOIN (SELECT @streak := 0, @last_date := '1900-01-01') i
      ORDER BY s.Date) s
WHERE s.Date = '2018-10-20'

Output:

Date        streak
2018-10-20  3

Demo on dbfiddle

Upvotes: 4

Related Questions