By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table transaction_table (
customer_id int,
transaction_date date,
spent int
);
insert into transaction_table (customer_id, transaction_date, spent) values (1, '2021-10-01', 2000);
insert into transaction_table (customer_id, transaction_date, spent) values (1, '2021-10-15', 1500);
insert into transaction_table (customer_id, transaction_date, spent) values (1, '2021-12-01', 1000);
insert into transaction_table (customer_id, transaction_date, spent) values (2, '2021-11-01', 2500);
select * from transaction_table;
| CUSTOMER_ID | TRANSACTION_DATE | SPENT |
|---|---|---|
| 1 | 2021-10-01 | 2000 |
| 1 | 2021-10-15 | 1500 |
| 1 | 2021-12-01 | 1000 |
| 2 | 2021-11-01 | 2500 |
select *
from (
select t.*,
sum(t.spent) over(
partition by customer_id
order by julian_day(transaction_date)
range between 30 preceding and current row
) as total_spend
from transaction_table t
) x
where total_spend >= 3000
| CUSTOMER_ID | TRANSACTION_DATE | SPENT | TOTAL_SPEND |
|---|---|---|---|
| 1 | 2021-10-15 | 1500 | 3500 |