SQL Oracle script optimization

Solution 1:

Are you aware of the Analytic Functions Windowing Clause?

I don't get the logic of your query, but I guess it might be possible without any self-joins. Have a look this query, it could be a starting point:

SELECT 
    COUNT(ID_TRAN) OVER (PARTITION BY SENDER, NVL(RECEIVER_2, RECEIVER) ORDER BY DATE_ACCEPT RANGE BETWEEN INTERVAL '10' DAY PRECEDING AND CURRENT ROW) AS CNT_10,
    COUNT(ID_TRAN) OVER (PARTITION BY SENDER, NVL(RECEIVER_2, RECEIVER) ORDER BY DATE_ACCEPT RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS CNT_30,
    COUNT(ID_TRAN) OVER (PARTITION BY SENDER, NVL(RECEIVER_2, RECEIVER) ORDER BY DATE_ACCEPT RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND CURRENT ROW) AS CNT_90,
    AVG(NVL(T.AMOUNT_2, T.AMOUNT)) OVER (PARTITION BY SENDER, NVL(RECEIVER_2, RECEIVER) ORDER BY DATE_ACCEPT RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS AVG_30,
    AVG(NVL2(T.RECEIVER_2, T.AMOUNT_2, T.AMOUNT)) OVER (PARTITION BY SENDER, NVL(RECEIVER_2, RECEIVER) ORDER BY DATE_ACCEPT RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND CURRENT ROW) AS AVG_90
FROM TRANSACTIONS

Note, RANGE BETWEEN INTERVAL '10' DAY PRECEDING AND CURRENT ROW) is equal to RANGE INTERVAL '10' DAY PRECEDING)

Another note, when I run your query on the sample data, then I get

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|ID_TRAN|SENDER|RECEIVER|RECEIVER_2|AMOUNT|AMOUNT_2|DATE_ACCEPT        |CNT_10|CNT_30|CNT_90|AVG_AMOUNT_10|AVG_AMOUNT_30|AVG_AMOUNT_90|MAX_AMOUNT_10|MAX_AMOUNT_30|MAX_AMOUNT_90|SEQNUM|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|1      |00010 |22222   |1112      |3000  |1000    |16.04.2021 14:01:00|0     |0     |0     |             |             |             |             |             |             |1     |
|1      |00010 |22222   |2114      |3000  |2000    |16.04.2021 14:01:00|0     |0     |0     |             |             |             |             |             |             |1     |
|2      |01236 |45872   |          |4000  |        |01.04.2021 22:01:00|0     |0     |0     |             |             |             |             |             |             |1     |
|3      |45872 |00010   |          |5000  |        |17.04.2021 14:01:00|0     |0     |0     |             |             |             |             |             |             |1     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

which looks quite pointless.