您如何查询MySQL数据库以确定用户是否已连续30天或100登录？

Question

How could you query a MySQL database to determine if a user has logged in for 30 consecutive days or 100? Same will apply for other user actions too.

Assuming a DB table like below where a record is created each day for each user active on that day.

How could I query SELECT * WHERE user_id = 1 AND action = login and find if there was 30 days or 100 days of logins where the days are in consecutive row with no day breaks in it?

User Stats Table

id  
user_id  
dateTime  
action (login, made post, up voted, down votes, made comment, etc...)

Answer 1

In MySQL, probably the easiest way to do this is to use variables. Something like this:

select us.*
from (select us.* ,
             (@rn := if(date(@d) = date(datetime), @rn,
                        if(date(@d) = date(datetime) - interval 1 day,
                           if(@d := date(@datetime), @rn + 1, @rn + 1)
                           if(@d := date(@datetime), 1, 1)
                          )
                       )
            ) as rn
      from userstats us cross join
           (select @d := '', @rn := 0) params
      where user_id = 1
      order by datetime
     ) us
where rn >= 100;