Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 13.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit



SELECT 1


create table test (
    user_ int,
    ord int, -- transaction date replacement
    amount int, 
    lag interval -- after previous transaction
);
insert into test values
       (1, 1, 10, '1h'::interval),
    (1, 2, 10, '.5y'::interval),
    (1, 3, 10, '1h'::interval),
    (1, 4, 10, '.5y'::interval),
    (1, 5, 10, '.1h'::interval),
    (1, 6, 10, '.1h'::interval),
    (2, 1, 10, '1h'::interval),
    (2, 2, 10, '.5y'::interval),
    (2, 3, 10, '.1h'::interval),
    (2, 4, 10, '.1h'::interval),
    (3, 1, 10, '1h'::interval)
;

CREATE TABLE



INSERT 0 11


with cte as(
select *, 
case when (lead(lag) over (partition by user_ order by ord)) >= interval '.5 year'
then 1 else 0  end  "flag" from test
),
cte1 as (
select *,
case when flag=(lag(flag,1) over (partition by user_ order by ord)) then 0 else 1 end "flag1"  from cte
)
select distinct on (user_) user_, sum(amount) over (partition by user_,grp order by ord) from (
select *, sum(flag1) over (partition by user_ order by ord) "grp" from cte1) t1
order by user_ , ord desc


user_
sum


1
30

2
30

3
10



SELECT 3