This (simplified) query filters all double date records and only shows the most recently created record for every date.

Table: purchase

id
date
created

Query

SELECT t.*
FROM purchase AS t
LEFT JOIN purchase AS t2
    ON t.date = t2.date
    AND t.created < t2.created
WHERE t2.date IS NULL
ORDER BY t.date

Sample data

id date---- created------------
1  20240503 2024-03-17 08:00:00
2  20240501 2024-03-17 07:00:00
3  20240502 2024-03-17 23:00:00
4  20240503 2024-03-17 11:00:00
5  20240501 2024-03-17 19:00:00
6  20240503 2024-03-17 10:00:00
7  20240504 2024-03-17 13:00:00
8  20240503 2024-03-17 16:00:00

Data after query

id date---- created------------
5  20240501 2024-03-17 19:00:00
3  20240502 2024-03-17 23:00:00
8  20240503 2024-03-17 16:00:00
7  20240504 2024-03-17 13:00:00

Now, I would like to add a record counter of all per date records before the query was executed. It’s basically a counter showing the number of dupes per date (+1).
The result should look like this:

Desired result

id date---- created------------ counts
5  20240501 2024-03-17 19:00:00 2
3  20240502 2024-03-17 23:00:00 1
8  20240503 2024-03-17 16:00:00 4
7  20240504 2024-03-17 13:00:00 1

I tried with subqueries or implementing some kind of “counter” in the LEFT JOIN, but didn’t succeed.
Is this counter possible at all?
If yes, a hint would be sufficient/appreciated.