Looking for help with MySQL query - Calculate timestamp count within grouped range

Question

Some people would think this is easy, but it was a struggle all night long. I have a sensor alert log and would like to generate a table with a simple count of events over 24 hours, 168 hours (1 week) and 336 hours (2 weeks) for each different sensor. I'm relatively new to MySQL (running on GoDaddy) and self-taught, and have tried every variation of Count, Unique, Case, etc. I can find on the web. Help me Obi-Wan...

Sample data:

Alert_timestamp	Sensor ID
2022-05-18 21:41:59	Sensor 1
2022-05-21 21:52:09	Sensor 1
2022-05-24 05:00:39	Sensor 2
2022-05-24 05:02:26	Sensor 1
2022-05-24 18:37:34	Sensor 4
2022-05-24 20:48:40	Sensor 1
2022-05-26 21:20:54	Sensor 2
2022-05-27 14:53:02	Sensor 1
2022-06-01 19:06:14	Sensor 4
2022-06-02 19:22:27	Sensor 1
...	...

Desired output (note: the counts below do not correspond to the table above). I want to see these even if the sensor has zero alerts (e.g. the sensor is present in the table but there are no events in the date range).

count of events that fall within these ranges

sensor	<24hrs	24 to 168 hours	168 to 336 hours
Sensor 1	1	1	0
Sensor 2	0	2	5
Sensor 3	0	0	0
Sensor 4	6	2	3

Thank you in advance!

Answer 1

Use conditional aggregation.

SELECT SensorID, 
       SUM(    Alert_timestamp >  CURRENT_TIMESTAMP - INTERVAL  24 HOUR)  `<24hrs`,
       SUM(    Alert_timestamp <= CURRENT_TIMESTAMP - INTERVAL  24 HOUR
           AND Alert_timestamp >  CURRENT_TIMESTAMP - INTERVAL 168 HOUR)  `24 to 168hrs`,
       SUM(    Alert_timestamp <= CURRENT_TIMESTAMP - INTERVAL 168 HOUR
           AND Alert_timestamp >  CURRENT_TIMESTAMP - INTERVAL 336 HOUR)  `168 to 336hrs`       
FROM table 
WHERE Alert_timestamp > CURRENT_TIMESTAMP - INTERVAL 336 HOUR
GROUP BY SensorID

If the table contains "future" rows, add the corresponding conditions to the first aggregate and WHERE.

The indexing of

(SensorID, Alert_timestamp) will be improved.