How could you query a MySQL database to determine if a user has logged in for 30 consecutive days or 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...)

The proposed duplicate has an answer where the values are one row per date. That doesn't seem to be this problem, because a user could log in multiple times on one day. — Gordon Linoff, Dec 23 '15 at 12:18

score 0 · Accepted Answer · answered Dec 23 '15 at 03:49

0

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;

answered Dec 23 '15 at 03:49

Gordon Linoff

1,242,037
58
646
786

thanks, looks like a crazy amount of processing! Would I possibly be better off to have a column that increments a counter when the action happens and resets it if the last date wasn't the previous day? Like user logs in so I save a last login date and increment a counter if that last login was the day before today. Logging in daily would increment it until the previous day isn't present which would reset there login counter. Just thinking out loud thanks for your SQL will test it! – JasonDavis Dec 23 '15 at 04:32
@JasonDavis . . . Such counters would obviously make it easier to answer such questions. However, they introduce complications and the information is not available on historic data. – Gordon Linoff Dec 23 '15 at 12:16
I see what you mean. For example SO has a graph on profile where you can hover and see daily values for things where the data must be stored daily somewhere. thanks – JasonDavis Dec 27 '15 at 02:53
In my app I need to do similar stats on multiple things and not just logins. Perhaps a table with user ID and a column for logins, posts, etc...if user does any of the actions on a day a record for that day is created, any other actions done on that day simply update the record for that day. So for today 5 logins on 1st login would create DB record for today. The next 4 logins would increment login counter for today and posts today would update that same record. Tomorrow a new record would be created for this user if they do any actions. Any thoughts on this approach? – JasonDavis Dec 27 '15 at 02:57
@JasonDavis . . . If you require fast access to summary records, then triggers maintaining summary data are often the best approach. – Gordon Linoff Dec 27 '15 at 22:15

How could you query a MySQL database to determine if a user has logged in for 30 consecutive days or 100?

1 Answers1