I am trying to write a script that counts every time a customer spends more than €1200 within 7 days. Once a customer exceeds the €1200 threshold the cumulative sum should reset. For example, if a customer exceeded €1200 on day 3 this counts as 1 and the cumulative sum should reset on day 4.
I have seen similar questions which cover a resetting cumulative sum. None of these solutions work with the 7 day rolling condition.
Example Data Set
create table test2
(
yyyymmdd DATE not null,
account_id NUMBER,
vol_eur NUMBER
);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 11:16:19', 'dd-mm-yyyy hh24:mi:ss'), 57642, 1500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 09:51:23', 'dd-mm-yyyy hh24:mi:ss'), 57645, 190);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 07:09:35', 'dd-mm-yyyy hh24:mi:ss'), 57645, 300);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('03-01-2018 14:58:14', 'dd-mm-yyyy hh24:mi:ss'), 57646, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('17-01-2018 13:30:44', 'dd-mm-yyyy hh24:mi:ss'), 57646, 130);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('03-01-2018 18:33:33', 'dd-mm-yyyy hh24:mi:ss'), 57647, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('04-01-2018 08:44:33', 'dd-mm-yyyy hh24:mi:ss'), 57647, 270);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('05-01-2018 19:28:08', 'dd-mm-yyyy hh24:mi:ss'), 57647, 800);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('13-01-2018 12:24:21', 'dd-mm-yyyy hh24:mi:ss'), 57647, 700);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('15-01-2018 10:52:50', 'dd-mm-yyyy hh24:mi:ss'), 57647, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('27-01-2018 12:07:20', 'dd-mm-yyyy hh24:mi:ss'), 57647, 500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('10-01-2018 21:14:46', 'dd-mm-yyyy hh24:mi:ss'), 57647, 690);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('30-01-2018 15:39:17', 'dd-mm-yyyy hh24:mi:ss'), 57647, 5500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('05-01-2018 19:43:38', 'dd-mm-yyyy hh24:mi:ss'), 57649, 300);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 17:54:30', 'dd-mm-yyyy hh24:mi:ss'), 57649, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('15-01-2018 19:38:36', 'dd-mm-yyyy hh24:mi:ss'), 57649, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('20-01-2018 13:26:34', 'dd-mm-yyyy hh24:mi:ss'), 57649, 1150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 17:09:54', 'dd-mm-yyyy hh24:mi:ss'), 57651, 300);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('28-01-2018 17:31:14', 'dd-mm-yyyy hh24:mi:ss'), 57651, 250);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('04-01-2018 13:39:06', 'dd-mm-yyyy hh24:mi:ss'), 57654, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('07-01-2018 13:18:26', 'dd-mm-yyyy hh24:mi:ss'), 57654, 200);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('13-01-2018 19:44:08', 'dd-mm-yyyy hh24:mi:ss'), 57654, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 16:18:05', 'dd-mm-yyyy hh24:mi:ss'), 57654, 150);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('28-01-2018 10:53:03', 'dd-mm-yyyy hh24:mi:ss'), 57654, 60);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('01-01-2018 12:09:00', 'dd-mm-yyyy hh24:mi:ss'), 57655, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('01-01-2018 17:01:27', 'dd-mm-yyyy hh24:mi:ss'), 57655, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('02-01-2018 19:30:31', 'dd-mm-yyyy hh24:mi:ss'), 57655, 200);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 15:52:29', 'dd-mm-yyyy hh24:mi:ss'), 57655, 1000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 16:58:52', 'dd-mm-yyyy hh24:mi:ss'), 57655, 500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('11-01-2018 14:26:30', 'dd-mm-yyyy hh24:mi:ss'), 57661, 2000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('12-01-2018 21:54:25', 'dd-mm-yyyy hh24:mi:ss'), 57661, 500);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('06-01-2018 16:46:25', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('10-01-2018 18:27:51', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('14-01-2018 18:52:14', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('20-01-2018 12:19:07', 'dd-mm-yyyy hh24:mi:ss'), 57666, 5000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('24-01-2018 18:38:40', 'dd-mm-yyyy hh24:mi:ss'), 57666, 2990);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('30-01-2018 18:36:01', 'dd-mm-yyyy hh24:mi:ss'), 57666, 1980);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('19-01-2018 18:48:44', 'dd-mm-yyyy hh24:mi:ss'), 57671, 2000);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('19-01-2018 23:41:56', 'dd-mm-yyyy hh24:mi:ss'), 57671, 100);
insert into test2 (yyyymmdd, account_id, vol_eur)
values (to_date('21-01-2018 19:22:51', 'dd-mm-yyyy hh24:mi:ss'), 57671, 5000);
commit;