I have a table Mytable, where id - id of member and dp_id - id of departament:
date id dp_id
2020-11-14 01:22:10.260 300000 002
2020-11-14 01:41:13.260 352575 001
2020-11-14 16:39:31.910 352575 001
2020-11-14 23:39:52.510 352575 001
2020-11-14 00:00:00.260 300000 002
2020-11-15 00:01:20.710 352575 001
2020-11-15 01:00:43.600 352575 001
2020-11-15 13:41:19.410 352575 002
If the number of operations in next 30 days from the first transaction for pair id - dp_id (352575 - 001) is more than 5, it should be marked as over_lim.
For example, if the first operation for id-dp_id was at 01: 41: 13.260, therefore, over the next 30 days, i need to count how many operations were there and mark each transaction if there were more than 5.
i.e. 2020-11-15 01: 00: 43.600 352575 001 operation will be the 5th operation from the the first entry by id-dp_id pair, therefore we mark it as over_lim. And so on for each period - we need to take the first transaction of the period and see how much it was in the next 30 days.
Expected output, where tr_count - count of each transaction per pair id-dp_id, over_lim- our mark, that it's a overlimit transaction(>=5):
date id dp_id tr_count over_lim
2020-11-14 01:22:10.260 300000 002 1 False
2020-11-14 01:41:13.260 352575 001 1 False
2020-11-14 16:39:31.910 352575 001 2 False
2020-11-14 23:39:52.510 352575 001 3 False
2020-11-14 00:00:00.260 300000 002 2 False
2020-11-15 00:01:20.710 352575 001 4 False
2020-11-15 01:00:43.600 352575 001 5 True
2020-11-15 13:41:19.410 352575 002 1 False
A similar approach gives me the result you want ( I change field date by date_oper )
SQL> with t as
2 (
3 select to_date('2020-11-14 01:22:10','yyyy-mm-dd hh24:mi:ss') date_oper, 300000 id, '002' dp_id from dual union all
4 select to_date('2020-11-14 01:41:13','yyyy-mm-dd hh24:mi:ss') date_oper, 352575 id, '001' dp_id from dual union all
5 select to_date('2020-11-14 16:39:31','yyyy-mm-dd hh24:mi:ss') date_oper, 352575 id, '001' dp_id from dual union all
6 select to_date('2020-11-14 23:39:52','yyyy-mm-dd hh24:mi:ss') date_oper, 352575 id, '001' dp_id from dual union all
7 select to_date('2020-11-14 00:00:00','yyyy-mm-dd hh24:mi:ss') date_oper, 300000 id, '002' dp_id from dual union all
8 select to_date('2020-11-15 00:01:20','yyyy-mm-dd hh24:mi:ss') date_oper, 352575 id, '001' dp_id from dual union all
9 select to_date('2020-11-15 01:00:43','yyyy-mm-dd hh24:mi:ss') date_oper, 352575 id, '001' dp_id from dual union all
10 select to_date('2020-11-15 13:41:19','yyyy-mm-dd hh24:mi:ss') date_oper, 352575 id, '002' dp_id from dual
11 )
12 select t.*,
13 count(*) over (partition by id, dp_id order by date_oper range between interval '30' day preceding and current row) as tr_count,
14 (case when count(*) over (partition by id, dp_id order by date_oper range between interval '30' day preceding and current row) >= 5
15 then 'true' else 'false'
16 end) as over_lim
17* from t order by date_oper
SQL> /
DATE_OPER ID DP_ TR_COUNT OVER_
------------------- ---------- --- ---------- -----
2020-11-14 00:00:00 300000 002 1 false
2020-11-14 01:22:10 300000 002 2 false
2020-11-14 01:41:13 352575 001 1 false
2020-11-14 16:39:31 352575 001 2 false
2020-11-14 23:39:52 352575 001 3 false
2020-11-15 00:01:20 352575 001 4 false
2020-11-15 01:00:43 352575 001 5 true
2020-11-15 13:41:19 352575 002 1 false
8 rows selected.
You can check here