Selecting the number of last consecutive days from timestamp (excepting today)

Question

I have a table A_DailyLogins with the columns ID (auto increment), Key (userid) and Date (timestamp). I want a query which would return the number of last consecutive days from those timestamp based on the Key, for example if he has a row for yesterday, one for two days ago and another one for three days ago, but the last one isn't from four days ago, it would return 3, because this is the number of last days the user was logged in.

My attempt was to create a query selecting the last 7 rows of the players ordered by Date DESC (this is what I wanted in the first place, but then I thought that it would be great to have all the last consecutive days), and then I retrieved the query result and compared the dates (converted to year/month/day with functions from that language [Pawn]) and increased the number of consecutive days when a date is before the other one with one day. (but this is extremely slow compared to what I think that can be done directly only with MySQL)

The closest thing I found is this: Check for x consecutive days - given timestamps in database . But it still isn't how I want it to be, it's still pretty different. I tried to modify it, but it is way too hard for me, I don't have that much experience in MySQL.

I don't understand what you want. I suggest you add an example with real data. — m0skit0, Jul 31 '15 at 21:01
Do you have the possibility to modify the database and/or the way how data is created? — dognose, Jul 31 '15 at 21:07
@dognose: Yes, but I guess that using timestamp is the best method. Anyway, I can modify it how I want. — Nistor Constantin, Jul 31 '15 at 21:12
@m0skit0: I have a table with columns `ID`, `Key` and `Date` (timestamp), and every day a player logins it inserts a new row if he didn't login before in that day. I want to get the amount of consecutive days the player logged in (but only the current consecutive days, not others too). — Nistor Constantin, Jul 31 '15 at 21:14

amdixon · Accepted Answer · 2016-01-03T06:14:32.993

context

let consecutive login period be a period where the user is logged in on all days ( has an entry in A_DailyLogins on every day in period ) where there is no entry in A_DailyLogins immediately before or after the consecutive login period with the same user

and number of consecutive days be the difference between the maximum and minumum dates in a consecutive login period

the maximum date of a consecutive login period has no login entry immediately after ( sequentially ) to it..

the minimum date of a consecutive login period has no login entry immediately previous ( sequentially ) to it..

plan

left join A_DailyLogins to itself using same user and sequential dates where right is null to find maximums

analogous logic to find minimums

calculate row ordering over minimums and maximums with appropriate order by

join maximums and minimums on row number

filter where maximum login is yesterday/today

calculate date_diff between maximum and minimum in range

left join users to above resultset and coalesce over the case where user does not have a consecutive login period ending yesterday/today

input

+----+------+------------+
| ID | Key  | Date       |
+----+------+------------+
| 25 | eric | 2015-12-23 |
| 26 | eric | 2015-12-25 |
| 27 | eric | 2015-12-26 |
| 28 | eric | 2015-12-27 |
| 29 | eric | 2016-01-01 |
| 30 | eric | 2016-01-02 |
| 31 | eric | 2016-01-03 |
| 32 | nusa | 2015-12-27 |
| 33 | nusa | 2015-12-29 |
+----+------+------------+

query

select all_users.`Key`, 
coalesce(nconsecutive, 0) as nconsecutive
from
(
  select distinct `Key`
  from A_DailyLogins
) all_users
left join
(
  select
  lower_login_bounds.`Key`,
  lower_login_bounds.`Date` as from_login,
  upper_login_bounds.`Date` as to_login,
  1 + datediff(least(upper_login_bounds.`Date`, date_sub(current_date, interval 1 day))
                     , lower_login_bounds.`Date`) as nconsecutive
  from
  (
    select curr_login.`Key`, curr_login.`Date`, @rn1 := @rn1 + 1 as row_number
    from A_DailyLogins curr_login
    left join A_DailyLogins prev_login
    on curr_login.`Key` = prev_login.`Key`
    and prev_login.`Date` = date_add(curr_login.`Date`, interval -1 day)
    cross join ( select @rn1 := 0 ) params
    where prev_login.`Date` is null
    order by curr_login.`Key`, curr_login.`Date`
  ) lower_login_bounds
  inner join
  (
    select curr_login.`Key`, curr_login.`Date`, @rn2 := @rn2 + 1 as row_number
    from A_DailyLogins curr_login
    left join A_DailyLogins next_login
    on curr_login.`Key` = next_login.`Key`
    and next_login.`Date` = date_add(curr_login.`Date`, interval 1 day)
    cross join ( select @rn2 := 0 ) params
    where next_login.`Date` is null
    order by curr_login.`Key`, curr_login.`Date`
  ) upper_login_bounds
  on lower_login_bounds.row_number = upper_login_bounds.row_number
  where upper_login_bounds.`Date` >= date_sub(current_date, interval 1 day)
  and   lower_login_bounds.`Date` < current_date
) last_consecutive
on all_users.`Key` = last_consecutive.`Key`
;

output

+------+------------------+
| Key  | last_consecutive |
+------+------------------+
| eric |                2 |
| nusa |                0 |
+------+------------------+

valid as run on 2016-01-03

sqlfiddle

Wow ! I didn't know this will be that complex ! This is great, it's exactly what I wanted. Thank you ! — Nistor Constantin, Aug 01 '15 at 12:34
yeah, can be a little tricky as mysql doesnt support windowed functions like row_number, etc. but its simple enough once you understand how mysql variables work and the lag variables pattern — amdixon, Aug 01 '15 at 12:41
A little problem: can you make it so it will count those consecutive days only if the last consecutive day (or the only one) is yesterday ? For example, now we are in 01-08-2015, I want it to count the consecutive days only if the last one is on 31-07-2015, not if it is on 30-07-2015 or earlier. If there is only yesterday, it should return 1 (or how many days are consecutive until yesterday), if the last is more days ago, it should return 0. — Nistor Constantin, Aug 01 '15 at 15:44
`then then @stop := 0', you mean that the both @stop there should be set to 0 ? I tried with different cases (1 and 0, 0 and 0, 0 and 1) and it is printing only 0. — Nistor Constantin, Aug 01 '15 at 17:20
@NistorConstantin added fiddle, please check output behaviour — amdixon, Aug 01 '15 at 17:25
It seems like my table got a little messed up, now it works (after I reinserted the values). Thanks ! I guess that it's all done now. — Nistor Constantin, Aug 01 '15 at 17:51
Now I see that in your example it works fine for "nusa", but for "eric" it doesn't work, after I changed the schema. It isn't alright. — Nistor Constantin, Aug 01 '15 at 19:53
@NistorConstantin corrected - issue was null handling with @lag_user with the first user. ie when it checks if @lag_user <> `Key` since @lag_user is null it returns false so adjusted this to also allow null. updated answer — amdixon, Aug 02 '15 at 02:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/84929/discussion-between-nistor-constantin-and-amdixon). — Nistor Constantin, Aug 02 '15 at 13:45

Selecting the number of last consecutive days from timestamp (excepting today)

1 Answers1