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
Do you have the possibility to modify the database and/or the way how data is created? — dognose
@dognose: Yes, but I guess that using timestamp is the best method. Anyway, I can modify it how I want. — Nistor Constantin
@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

amdixon amdixon · Accepted Answer · 2015-08-01T05:53:54

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

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

1 Answers