Select Count (case when)! Friends, I would like help from you to find a solution to the following case:

A table with 2 fields: datetime, record (the record being any number that can be repeated a few times)

I need to count how many times each record appears in 12h, 24h, and 72h intervals.

Example, supposing record number 1: It appears 2 times in a 12h interval, plus 1 within 24h and another 3 in the 72h interval.

Thus, for the first record 1, in the 12-hour column there would be a value of 2, a 24-hour value would be 3, a 72-hour value would be 6. In the second row of record 1 the results would be: for 12h value 1, 24h value 2, 72h value 5 and so on.

Any suggestion?
I can share the conceptual answer to this problem, you will have to fill in correct syntax for query as per your database engine.

select record, count(record)
from table
where datetime is interval of 12 hours from now
group by record


This query can give you record occurrences in one single interval of time.

select record, 
   SUM(case when datetime is in interval 12h then 1 else 0 end) 12_hour_occurrence,
   SUM(case when datetime is in interval 24h then 1 else 0 end) 24_hour_occurrence,
   SUM(case when datetime is in interval 72h then 1 else 0 end) 72_hour_occurrence
from table
where datetime &gt; now() - 72 hours # don't pass thorugh extra records
group by record


For running on single query on multiple instances, you can use this concept to find occurrences in multiple intervals.