SQL Query to get the count of events in the last 30days per user based on the max date

Question

I have an event table that looks like this.

| id | event_id| date       |
|----|---------|------------|
| 1  | d-paiin | 28/01/2022 |
| 1  | 123gin  | 02/01/2022 |
| 1  | dhsdel  | 01/01/2022 |
| 1  | sigshwy | 31/12/2021 |
| 1  | dsiwuef | 31/12/2021 |
| 2  | shau346 | 10/02/2022 |
| 2  | akdnjsfm| 04/02/2022 |
| 2  | asjufbe | 04/02/2022 |
| 2  | jladiurn| 01/02/2022 |

I want to write a query that finds id and their number of events in the last 30days using the MAX Date of each user as a reference point. i.e count of event per id where date > (MAX(Date) of each user) - 30 days.

Expected result looks like this:

| id | count_event|
| 1  | 5          |
| 2  | 4          |

Thanks.

Answer 1

If by MAX(Date) of each user you mean the current date at a given time when the user accesses the system, this code works just fine:

SELECT      id, 
            COUNT(id) AS count_event
FROM        tablename
WHERE       DATEDIFF(SYSDATE(), tablename.date) < 30
GROUP BY    id;

You can also use GETDATE() instead of SYSDATE() for current time.

Answer 2

The first select searches for a max date for each user.
The second select filter records in the range of 30 days by previously obtained values.

with f as (
 select
  "id", max("date") as md
 from mytable
 group by "id"
)
select e.id, count(e.id)
from mytable as e left join f on e.id = f.id
where e.date > f.md - 30
group by e.id
order by 1
;

Output:

id	count
1	5
2	4

DDL:

I added older values to show that the query really only takes 30 days.

CREATE TABLE mytable
    ("id" varchar(4), "event_id" varchar(9), "date" date)
;
    
INSERT INTO mytable
    ("id", "event_id", "date")
VALUES
    ('1', 'd-iin', to_date('28/01/2020','dd/mm/yyyy')),
    ('1', 'd-paiin', to_date('28/01/2022','dd/mm/yyyy')),
    ('1', '123gin', to_date('02/01/2022','dd/mm/yyyy')),
    ('1', 'dhsdel', to_date('01/01/2022','dd/mm/yyyy')),
    ('1', 'sigshwy', to_date('31/12/2021','dd/mm/yyyy')),
    ('1', 'dsiwuef', to_date('31/12/2021','dd/mm/yyyy')),
    ('2', 'shau3', to_date('5/01/2022','dd/mm/yyyy')),
    ('2', 'shau346', to_date('10/02/2022','dd/mm/yyyy')),
    ('2', 'akdnjsfm', to_date('04/02/2022','dd/mm/yyyy')),
    ('2', 'asjufbe', to_date('04/02/2022','dd/mm/yyyy')),
    ('2', 'jladiurn', to_date('01/02/2022','dd/mm/yyyy'))
;