Could anybody give me an idea or hint how you could check for X consecutive days in a database table (MySQL) where logins (user id, timestamp) are stored?
Stackoverflow does it (e.g. badges like Enthusiast - if you log in for 30 consecutive days or so...). What functions would you have to use or what is the idea of how to do it?
Something like SELECT 1 FROM login_dates WHERE ...?
Wouldn't it be more simple to have an extra column consecutive_days in login_dates table with default value 1. This would indicate the length of consecutive dates ending on that day.
You create an insert after trigger on login_dates where you check if there is an entry for the previous day.
If there is none, then the field would have the default value 1 meaning that a new sequence is started on that date.
If here is an entry for previous day then you change the days_logged_in value from the default 1 to be 1 greater then that of previous day.
Ex:
| date | consecutive_days |
|------------|------------------|
| 2013-11-13 | 5 |
| 2013-11-14 | 6 |
| 2013-11-16 | 1 |
| 2013-11-17 | 2 |
| 2013-11-18 | 3 |