Hi - I have a requirement of bucketing transactions based on a five day window. If a transaction starts for a serial number on a particular day, any transactions from that day for next five days should be marked as Bucket 1, and if for the same serial number if a transaction takes place after the first 5 day bucket, it should be marked as bucket 2. Sample data is given as below.
For serial number 908115 there should be two buckets. First window is 1/11 - 1/16. There are 4 transactions within that range. Second set starts on 1/20 and the range is 1/20-1/25. it should be marked as bucket 2. Similartly seond serial number 998763 has only one bucket 1/15- 1/20.
Can this be acheived thru a window analytical function? If possible I want to avoid PLSQL. Please help!.
create table test_tbl(trans_dt date, s_num number);
insert into test_tbl values (to_date('01/11/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/12/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/13/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/15/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/20/2019','MM/DD/YYYY'),989115);
insert into test_tbl values (to_date('01/22/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/24/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/25/2019','MM/DD/YYYY'),908115);
insert into test_tbl values (to_date('01/15/2019','MM/DD/YYYY'),998763);
insert into test_tbl values (to_date('01/17/2019','MM/DD/YYYY'),998763);
insert into test_tbl values (to_date('01/19/2019','MM/DD/YYYY'),998763);
insert into test_tbl values (to_date('01/20/2019','MM/DD/YYYY'),998763);