295139-calculate-a-cumulative-amount-with-a-given-time

295139-calculate-a-cumulative-amount-with-a-given-time.sql

USE ROLE MY_ROLE_NAME;
USE WAREHOUSE MY_WAREHOUSE_NAME;
USE DATABASE MY_DATABASE_NAME;

CREATE SCHEMA IF NOT EXISTS demo;

create or replace table demo.customer_transactions(
  transaction_id integer identity
,customer varchar
,txn_date timestamp_ltz
,amount float
);

//
//CUSTOMER         STAMP        AMOUNT
//--------         -----        ------
insert into demo.customer_transactions (customer, txn_date, amount)
values
('A',     '2021-06-02 00:22:53',    1.44)
,('A',     '2021-06-02 06:24:17',    1.51)
,('A',     '2021-06-03 07:09:45',    2.73)
,('A',     '2021-06-03 15:57:30',    3.92)
,('A',     '2021-06-04 06:41:21',    1.83)
,('B',     '2021-06-01 02:50:22',    2.65)
,('B',     '2021-06-03 07:01:36',    4.05)
,('B',     '2021-06-04 05:20:10',   3.30)
,('B',     '2021-06-04 09:53:53',    2.64)
,('B',     '2021-06-04 14:54:00',    2.26)
,('C',    '2021-06-01 16:01:38',    2.61)
,('C',     '2021-06-01 23:38:25',    1.16)
,('C',     '2021-06-02 14:41:02',    2.82)
,('C',     '2021-06-03 00:28:37',    1.54)
,('C',     '2021-06-03 02:06:46',    1.19)
,('C',     '2021-06-04 17:16:29',    2.05)
;


select
*
,case when window_transaction_amount > 10.00 then 'warn' else 'ok' end as is_alert
from
(
select 
 a.transaction_id
,a.customer
,a.txn_date
,a.txn_date as window_start_date
,dateadd(hour,48, a.txn_date) as window_end_date
,a.amount
//,array_construct(b.transaction_id) as b_transactions
,listagg(distinct b.transaction_id,',') within group (order by b.transaction_id) as window_transaction_ids
,sum(b.amount) as window_transaction_amount
from demo.customer_transactions a left outer join demo.customer_transactions b on a.customer = b.customer and b.txn_date between a.txn_date and dateadd(hour,48, a.txn_date)
group by 
  a.transaction_id
,a.customer
,a.txn_date
,dateadd(hour,48, a.txn_date)
,a.amount
) q
order by 1,2,3;