CODEWITHSUNDEEP
mysqlsql
Kofi Black
Kofi Black

Reputation: 121

Get all streaks of consecutive dates from a user score table

Table score_streak is used to store a user's daily scores and is defined as follows.

CREATE TABLE IF NOT EXISTS score_streak(
    create_date DATE NOT NULL,
    score INT(11),
    PRIMARY KEY (create_date)
);

insert into score_streak (create_date, score) values 
    (DATE('2017-04-01'), 11) ,
    (DATE('2017-04-02'), 8) ,
    (DATE('2017-04-03'), 9) ,
    (DATE('2017-04-06'), 14) ,
    (DATE('2017-04-07'), 15) ,
    (DATE('2017-04-08'), 13) ,
    (DATE('2017-04-12'), 20) ,
    (DATE('2017-04-13'), 21) ,
    (DATE('2017-04-14'), 22) ,
    (DATE('2017-04-15'), 18) ;

select * from score_streak;
    create_date | score
    2017-04-01  |   11
    2017-04-02  |   8
    2017-04-03  |   9
    2017-04-06  |   14
    2017-04-07  |   15
    2017-04-08  |   13
    2017-04-12  |   20
    2017-04-13  |   21
    2017-04-14  |   22
    2017-04-15  |   18

I would like to query the table to get all streaks in which the user's score is greater than or equal to 10 and the dates must be consecutive. And each streak has a start date and an end date.

For example, the expected result of the sample data above is provided below (note that there are 3 streaks):

start_date | end_date   |streak_count
2017-04-01 | 2017-04-01 |     1
2017-04-06 | 2017-04-08 |     3
2017-04-12 | 2017-04-15 |     4

Thanks.

Upvotes: 1

Views: 238

Answers (1)

peterm
peterm

Reputation: 92805

You can do

SELECT MIN(create_date) start_date, 
       MAX(create_date) end_date, 
       COUNT(*) streak_count
  FROM (
    SELECT q.*,
           @g := @g + COALESCE(DATEDIFF(create_date, @p) <> 1, 0) gn,
           @p := create_date
      FROM (
        SELECT *
          FROM score_streak
         WHERE score > 9
         ORDER BY create_date
    ) q CROSS JOIN (
        SELECT @g := 0, @p := NULL
    ) i
) r
 GROUP BY gn

Output:

+------------+------------+--------------+
| start_date | end_date   | streak_count |
+------------+------------+--------------+
| 2017-04-01 | 2017-04-01 |            1 |
| 2017-04-06 | 2017-04-08 |            3 |
| 2017-04-12 | 2017-04-15 |            4 |
+------------+------------+--------------+

SQLFiddle

Upvotes: 2

Related Questions