Find Interest rate on account statement in sql

i hava table of statement and it has the date,credit,debit and balance column through these i want to find total interest.

TRX_DATE     CREDIT      DEBIT        BAL
-------- ---------- ---------- ----------
01-11-18                                0
05-11-18       7500                  7500
08-11-18                  2500       5000
15-11-18      10000                 15000
25-11-18                  9000       6000
30-11-18                  1000       5000
05-12-18       7100                 12100
10-12-18                  2100      10000
20-12-18      20000                 30000
25-12-18                 15000      15000

for finding interest :

1. if count_day=next_date-current_date then
2. (4*(sum(count*bal)))/36500

I tried and get row of each trx interest but didn’t get total or sum of the rows. and while trying sum() on the query error shows ORA-30483: window functions are not allowed here

select ((4*(bal*(trx_date-((lag(trx_date) over (order by trx_date))))))/36500)as interest 
from int i;

interest
------------
78.

Answer

Here is my solution using LEAD

SELECT ROUND(4.0 * SUM(bal * days / 36500), 2) as Interest
FROM (
    SELECT bal, NVL(LEAD(trx_date) OVER (ORDER BY trx_date) - trx_date, 0) days
    FROM transaction)

This gives an accrued interest of 62.79 for the sample data. Below are the individual interest amounts

+----+--------+-------+  
|DAYS|     BAL|    INT|  
+----+--------+-------+  
|   4|       0|      0|  
|   3|    7500|   2.47|  
|   7|    5000|   3.84|  
|  10|   15000|  16.44|  
|   5|    6000|   3.29|  
|   5|    5000|   2.74|  
|   5|   12100|   6.63|  
|  10|   10000|  10.96|  
|   5|   30000|  16.44|  
|   0|   15000|      0|  
+----+--------+-------+