Search code examples
mysqlsqlgroup-bydate-sub

Were there n number of entries made within x hours in last y hours

Let's say there is a table with entries. A user is not allowed to make more than 20 entries in 24 hours. If a user exceed this limit it will be banned for the next 24 hours.

id  user  datetime     
---|-----|-----------------
1  | 10  | 2021-06-01 23:01 
2  | 11  | 2021-06-01 23:04 
5  | 10  | 2021-06-01 23:31 
3  | 12  | 2021-06-01 23:59 
4  | 21  | 2021-06-02 00:01 
6  | 10  | 2021-06-02 00:09 
7  | 18  | 2021-06-02 01:15 
8  | 13  | 2021-06-02 03:02

I would like to check this (if possible) with one query. As I imagine it, I should check if a user has made at least 20 entries within 24 hours in the last 48 hours. But how?

Solution

  • You can use:

    select count(*), max(datetime)
from t
where user = ? and
      datetime >= now() - interval 24 hour;

    When this hits your threshold you can ban the user.