How to edit this mySQL query so as to find people who visited specific page more than x times?

Question

I am trying to make a complex (for me) MySQL query to get some data from the database. I have 3 columns that I want to mix so as to get the data.

1. timestamp column
2. url column
3. hash column

My end goal is to answer these type of questions:

• How many and who are those people who visited a specific URL, in a past days range, but those people must accessed that page x times in that same past days range.

I have done this so far,but i do not know how to have the total for each hash

    SELECT DISTINCT hash
    FROM behaviour
    WHERE DATE( TIMESTAMP ) BETWEEN
    SUBDATE( CURDATE( ) , 2 ) AND CURDATE( )
    AND url =  '/used-results' 

AND the 'url' is found more than 2 times for this hash

How can i edit my query so as to add the last condition?

Answer 1

If you want to count pages, you are going to need aggregation functions, somewhere along the line. So:

SELECT hash
FROM behaviour
WHERE DATE( TIMESTAMP ) BETWEEN SUBDATE( CURDATE( ) , 2 ) AND CURDATE( ) AND
      url =  '/used-results'
GROUP BY hash
HAVING COUNT(*) > 2;

Typically a table like this would be pretty big and might have an index on (url, TimeStamp). So, the following version makes better use of hte index:

SELECT hash
FROM behaviour
WHERE url =  '/used-results' AND
      TIMESTAMP >= DATE_SUB(CURDATE( ), INTERVAL 2 DAY ) AND 
      TIMESTAMP < DATE_ADD(CURDATE( ), INTERVAL 1 DAY)          
GROUP BY hash
HAVING COUNT(*) > 2;