I have 3 Mysql tables.
visits
Records the IP of the user, date, the ID of the item they have visited.
| id | item_id | ip_address | fecha |
|------------------------------------------------------|
| 1 | 3 | 123.123.123.123 | 2018-03-01 17:00:30 |
| 1 | 1 | 123.123.123.123 | 2018-03-03 17:00:30 |
| 1 | 5 | 000.000.000.000 | 2018-03-04 17:00:30 |
| 1 | 4 | 123.123.123.123 | 2018-03-01 17:00:30 |
| 1 | 4 | 999.999.999.999 | 2018-03-02 17:00:30 |
| 1 | 1 | 888.888.888.888 | 2018-03-12 17:00:30 |
items
It has data about the items, among them: it has the author_id (the ID of the creator of the item).
| id | autor_id |
-----------------
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
Authors
This table has the list of authors (creators) with their data.
| id | name | surname |
-------------------------
| 1 | Jose | Rodriguez |
| 2 | Jesus| Abadia |
I need to get: how many unique daily visits an author has gotten in the last 30 days. This is what I've come up with:
SELECT item_id, ip_address FROM `visitas`
WHERE 1
AND date > DATE_SUB(NOW(),INTERVAL 30 DAY)
GROUP BY item_id, ip_address
For example:
| fecha | visitas |
--------------------------
| 2018-03-01 | 2 |
| 2018-03-02 | 10 |
| 2018-03-02 | 8 |
...
if all you need is the SQL query, you can use the following:
With this query you obtain the number of visits that an author received per day from different IP addresses in the last 30 days, the only input data is the author's id, if you need me to explain any part of the code I can edit the answer, I hope May it be of help to you, greetings.
The solution proposed by @the-beaker works perfectly except the last two lines. I get the following error:
But modifying a little it works fine!