Run rate calculate in pgsql-CodePudding

I Have this table:

CREATE TABLE data 
    (
        Event_Date date,
        approved int, 
        rejected int
    )
    
    INSERT INTO data (Event_date, approved, rejected)
    VALUES
        ('20190910', '5', '2'),
        ('20190911', '6', '3'),
        ('20190912', '5', '2'),
        ('20190913', '7', '5'),
        ('20190914', '8', '4'),
        ('20190915', '10', '2'),
        ('20190916', '4', '1')

How to make a loop or something else for calculate run rate and get results(in Rolling monthly rate CL I write how formula need to be use) like this:

Event_date   approved,      rejected    Rolling monthly rate
------------------------------------------------------------
20190901           5           2           ---
20190902           6           3           6 5/5 6 2 3
20190903           4           2           6 4/6 3 4 2
20190903           7           5           7 4/4 2 7 5
20190904           8           4           8 4/7 5 8 4
20190905           10          2           ....
20190906           4           1           .....

CodePudding user response：

The lag() function, which returns the previous value, is perfect for this task. You need to write a case when statement and skip the first entry since there is no previous value and then calculate using the desired formula.

select *, case when row_number() over() > 1
          then  approved   lag(approved) over() / approved   rejected   lag(approved)  over()   lag(rejected)  over()
          end   as rate
from my_table

Demo in DBfiddle