Find max of continuous streak and the current streak from datetime

Question

I have the following data of a particular user - Table temp -

time_stamp
2015-07-19 10:52:00
2015-07-18 10:49:00
2015-07-12 10:43:00
2015-06-08 12:32:00
2015-06-07 11:33:00
2015-06-06 10:05:00
2015-06-05 04:17:00
2015-04-14 04:11:00
2014-04-02 23:19:00

So the output for the query should be - Maximum streak = 4, Current streak = 2

Max streak = 4 because of these -

2015-06-08 12:32:00
2015-06-07 11:33:00
2015-06-06 10:05:00
2015-06-05 04:17:00

And current streak is 2 because of these (Assuming today's date is 2015-07-19)-

2015-07-19 10:52:00
2015-07-18 10:49:00

EDIT: I want a simple SQL query for MYSQL

StackOverflow is not *I want code, give me code* kind of website. It's more like *I want to achieve that, I tried following and that's where I'm stuck.*. Show what you're tried, tell us what problem you have with your approach. — MarcinJuraszek, Jul 19 '15 at 00:06
@MarcinJuraszek funny. The site founder asked a similar question and didn't include any of that. http://stackoverflow.com/q/1176011/73226 — Martin Smith, Jul 19 '15 at 00:07
Yes there can be more than one entry per day. @shawnt00 - Please don't consider any other field(column) except `time_stamp`and the table name is `temp`. Only the count is required. — raj454raj, Jul 19 '15 at 01:01
@raj454raj I'm pretty sure you can handle adapting the query for yourself. — shawnt00, Jul 19 '15 at 15:02

score 2 · Answer 1 · edited Nov 09 '19 at 17:13

For MAX streak(streak) you can use this, I have use the same query to calculate max streak. This may help you

SELECT *
FROM (
   SELECT t.*, IF(@prev + INTERVAL 1 DAY = t.d, @c := @c + 1, @c := 1) AS streak, @prev := t.d
   FROM (
       SELECT date AS d, COUNT(*) AS n
       FROM table_name
       group by date

   ) AS t
   INNER JOIN (SELECT @prev := NULL, @c := 1) AS vars
) AS t
ORDER BY streak DESC LIMIT 1;

score 0 · Answer 2 · answered Jul 19 '15 at 00:42

A general approach with the gaps and islands queries is to tag each row with its rank in the data and with its rank in the full list of dates. The clusters will all have the same difference.

Caveats: I don't know if this query will be efficient. I don't remember if MySQL allows for scalar subqueries. I didn't look up the way to calculate a day interval in MySQL.

select user_id, max(time_stamp), count(*)
from (
    select
        t.user_id, t.time_stamp,
        (
            select count(*)
            from T as t2
            where t2.user_id = t.user_id and t2.time_stamp <= t.time_stamp
        ) as rnk,
        number of days from t.time_stamp to current_date as days
    from T as t

) as data
group by usr_id, days - rnk

Find max of continuous streak and the current streak from datetime

2 Answers2