I have a platform that has a check-in function. I need to determine how many consecutive days this user has checked in on this platform and give him different points.
For example, today is April 26th, and I have signed in every day since April 15th. That means I have signed in for 12 consecutive days. I also signed in on April 13th, but did not sign in on April 14th.
mysql statement How to write it
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Generally, the number of days of continuous check-in is stored. If it is not stored, one way we have done before is to store the check-in with a timestamp and then get the timestamp of the check-in. Generally, we get it for one month (if this month When the sign-in is full, get the previous month's number), and then loop through the number of days in PHP to calculate it. Later, this method was slower, so we recorded the check-in days separately
Database design issues.
Because it is necessary to count consecutive days, it is impossible to record one item every day (imagine a bank recording the user's balance for any number of days).
The accounting method is called the annual accumulation algorithm. That is, recording a certain point in time until another time. The balance at one point in time is unchanged (continuous)
The table design is roughly as follows:
End time-start time = number of days. Points conversion is just like tax, just calculate the bonus.
In your business rules, there should be an upper limit on how many points will be given for x consecutive days. For example, how many points will be given if there are >= 20 consecutive days.
Then let’s remember this x, in db select * from tb where uid = 10086 order by dt desc limit x
Then judge the maximum number of consecutive days in the program and add the corresponding score.