MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table transactions(transaction_id int, customer_id int, date datetime, should_aggregate boolean);
insert into transactions values (1,1,'2023-01-10 01:00:00',true);
insert into transactions values (2,1,'2023-01-10 00:55:00',false);
insert into transactions values (3,1,'2023-01-09 00:57:00',true);
insert into transactions values (4,1,'2023-01-07 00:57:00',false);
insert into transactions values (5,2,'2023-01-10 00:57:00',true);
SELECT
  transaction_id
  , customer_id
  , date
  , (SELECT count(*) FROM transactions t2 WHERE t2.customer_id = t1.customer_id AND t2.date > DATE_SUB(t1.date, INTERVAL 1 HOUR) AND t2.date < t1.date) previous_1hour
  , (SELECT count(*) FROM transactions t2 WHERE t2.customer_id = t1.customer_id AND t2.date > DATE_SUB(t1.date, INTERVAL 1 DAY) AND t2.date < t1.date) previous_1day
  , (SELECT count(*) FROM transactions t2 WHERE t2.customer_id = t1.customer_id AND t2.date > DATE_SUB(t1.date, INTERVAL 1 WEEK) AND t2.date < t1.date) previous_1week
FROM transactions t1
WHERE should_aggregate


transaction_id
customer_id
date
previous_1hour
previous_1day
previous_1week


1
1
2023-01-10 01:00:00
1
1
3

3
1
2023-01-09 00:57:00
0
0
1

5
2
2023-01-10 00:57:00
0
0
0


SELECT
 t1.transaction_id
 , t1.customer_id
 , t1.date
 , SUM(CASE WHEN t2.date > DATE_SUB(t1.date, INTERVAL 1 HOUR) THEN 1 ELSE 0 END) previous_1hour
 , SUM(CASE WHEN t2.date > DATE_SUB(t1.date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) previous_1day
 , SUM(CASE WHEN t2.date > DATE_SUB(t1.date, INTERVAL 1 WEEK) THEN 1 ELSE 0 END) previous_1week
FROM transactions t1 LEFT OUTER JOIN transactions t2
  ON t1.customer_id = t2.customer_id AND t2.date < t1.date
WHERE t1.should_aggregate
GROUP BY t1.transaction_id, t1.customer_id, t1.date


transaction_id
customer_id
date
previous_1hour
previous_1day
previous_1week


1
1
2023-01-10 01:00:00
1
1
3

3
1
2023-01-09 00:57:00
0
0
1

5
2
2023-01-10 00:57:00
0
0
0

transaction_id	customer_id	date	previous_1hour	previous_1day	previous_1week
1	1	2023-01-10 01:00:00	1	1	3
3	1	2023-01-09 00:57:00	0	0	1
5	2	2023-01-10 00:57:00	0	0	0