CODEWITHSUNDEEP
mysqlsql
Brandon Benefield
Brandon Benefield

Reputation: 1672

MySQL: Return user making over X requests in Y amount of time

If I had a list of users coming back from my DB, is it possible to filter out and return the user(s) that appear over X amount of times in Y amount of time?

Here's an example of what I'm trying to accomplish:

mysql> SELECT * FROM user_activity;
+----------+---------------------+
| username | created_at          |
+----------+---------------------+
| User A   | 2018-12-06 00:00:00 |
| User B   | 2018-12-06 00:00:00 |
| User B   | 2018-12-06 00:09:00 |
| User A   | 2018-12-06 00:11:00 |
+----------+---------------------+
4 rows in set (0.00sec)

In MySQL, How would I return User B because this user appears more than once in a 10minute period? User A would be omitted from this result because they only appear once every 10minutes.

Upvotes: 0

Views: 37

Answers (3)

GMB
GMB

Reputation: 222582

You need an aggregate query, with a HAVING clause to filters users based on their number of occurences. The filter on the date goes to the WHERE clause.

The folowing query selects all users having at least 2 entries within 10 minutes after the given @report_date :

set @report_date = "2018-12-06 00:00:00";

SELECT username
FROM user_activity
WHERE created_at 
    BETWEEN DATE_ADD(@report_date, INTERVAL 10 MINUTE) 
    AND @report_date
GROUP BY username
HAVING COUNT(*) > 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

I would think about using exists:

select ua.*

from user_activity ua
where (select count(*)
       from user_activity ua2
       where ua2.user_name = ua.user_name and
             ua2.created_at <= ua.created_at and
             ua2.created_at > ua.created_at - interval 10 minute
      ) > 1;

This method is flexible. Your question is generally about having n occurrences during a period. Normally, I advocate using exists when someone wants a single occurrence, but for the general question, count(*) is a respectable approach.

Upvotes: 0

fifonik
fifonik

Reputation: 1606

SELECT
    DISTINCT a.username
FROM
    user_activity a
    INNER JOIN user_activity b ON (
            b.username = a.username
        AND b.created_at > a.created_at
        AND b.created_at <= DATE_ADD(a.created_at, INTERVAL 10 MINUTE)
    )

Upvotes: 0

Related Questions