Replacing self joins by window functions

Solution 1:

Check this solution with over(order by dt rows between unbounded preceding and 1 preceding):

with data as (
    select $1 dt, $2 ship, $3 audit, $4 action
    from values('2022-01-02', 1, 'id1', 'destroy')
    , ('2022-01-01', 1, 'id1', 'create')
    , ('2021-12-12', 2, 'id2', 'create')
    , ('2020-12-16', 2, 'id2', 'destroy')
    , ('2020-12-28', 2, 'id3', 'create')
)

select dt
    , sum(iff(action='create',1,0)) over(order by dt rows between unbounded preceding and 1 preceding) created_cnt
    , sum(iff(action='destroy',1,0)) over(order by dt rows between unbounded preceding and 1 preceding) destroyed_cnt
from data

Solution 2:

An alternative answer using PIVOT instead of IFF(). Would be interested to hear which approach scales best for your problem.

Code (Copy|Paste|Run):

with data as (
select $1 dt, $2 ship, $3 audit, $4 action
from values('2022-01-02', 1, 'id1', 'destroy')
, ('2022-01-01', 1, 'id1', 'create')
, ('2021-12-12', 2, 'id2', 'create')
, ('2020-12-16', 2, 'id2', 'destroy')
, ('2020-12-28', 2, 'id3', 'create')
)

select 
  dt 
, sum($3) over (order by dt rows between unbounded preceding and 1 preceding) created_cnt
, sum($4) over (order by dt rows between unbounded preceding and 1 preceding) destroyed_cnt
from 
data pivot ( count (audit) for action in ('create','destroy'));