Postgresql – Rolling sum aggregates with self-referencing conditions

aggregatectepostgresqlrecursivewindow functions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments aren't directly tied to the payments in the schedule. To start, we have a schedule object:

CREATE TABLE sch AS
SELECT ctr_id::int , mth::date , pmt_amt::numeric
FROM (
    VALUES
        ( 1 , '2019-01-01' , 145.0 ) ,
        ( 1 , '2019-02-01' , 145.0 ) ,
        ( 1 , '2019-03-01' , 145.0 ) ,
        ( 1 , '2019-04-01' , 145.0 ) ,
        ( 1 , '2019-05-01' , 145.0 ) ,
        ( 1 , '2019-06-01' , 145.0 )
) AS sch ( ctr_id , mth , pmt_amt )  ;

This table keeps track of the amortization schedule for a given contract (ctr_id). There is a separate payments object keeping track of each payment made towards a contract:

CREATE TABLE pmt AS
SELECT ctr_id::int , dt::date , amt::numeric
FROM (
    VALUES
        ( 1 , '2019-01-04' , 145.0 ) ,
        ( 1 , '2019-02-01' , 145.0 ) ,
        ( 1 , '2019-03-01' , 145.0 ) ,
        ( 1 , '2019-03-29' , 145.0 ) ,
        ( 1 , '2019-05-03' , 145.0 ) ,
        ( 1 , '2019-06-07' , 145.0 )
) AS sch ( ctr_id , dt , amt )  ;

When looking at this data in its totality, it is fairly easy to identify the 3-29 payment as being directed towards the April payment. However, I'm having trouble implementing logic that accounts for both this scenario and the next:

SELECT ctr_id::int , dt::date , amt::numeric
FROM (
    VALUES
        ( 1 , '2019-01-01' , 145.0 ) ,
        ( 1 , '2019-05-01' , 435.0 ) ,
        ( 1 , '2019-05-03' , 145.0 ) ,
        ( 1 , '2019-06-01' , 145.0 )
) AS sch ( ctr_id , dt , amt )  ;

In this case, it is obvious the first payment in May is a catch-up payment and the second payment is for May itself. The analytic SQL I started with:

SELECT
    sch.mth ,
    sch.ctr_id ,
    SUM( sch.pmt_amt - pmt.pmts ) OVER ( PARTITION BY sch.ctr_id ) AS outstanding
FROM
    sch
LEFT JOIN
(
    SELECT
        ctr_id ,
        DATE_TRUNC( 'month' , dt ) AS mth ,
        SUM( amt ) OVER ( PARTITION BY ctr_id ) AS pmts ,
        COUNT( * ) AS pmt_cnt
    FROM
        pmt
    GROUP BY
        ctr_id , DATE_TRUNC( 'month' , dt )
)
    AS pmt
        ON  pmt.ctr_id = sch.ctr_id
        AND pmt.mth = sch.mth

To address the first scenario, if the previous month had more than 1 payment and the total of the two payments is greater than the scheduled amount, apply the excess amount to the next month. For the second scenario, we must also consider the outstanding amount of the previous period. In pseudo-SQL:

SELECT
    sch.mth ,
    sch.ctr_id ,
    CASE
        WHEN LAG(pmt.pmt_cnt) OVER (ctr) > 1 AND pmt.pmts > sch.pmt_amt
        THEN pmt.pmts - sch.pmt_amt
        ELSE 0
    END AS carryover ,
    SUM(
        CASE
            WHEN LAG(outstanding) OVER (ctr) > 0
                THEN ( LAG(outstanding) OVER (ctr) + sch.pmt_amt ) - pmt.pmts
            ELSE sch.pmt_amt - pmt.pmts - LAG(carryover) OVER (ctr)
    ) OVER ( ctr ) AS outstanding
FROM
    sch
LEFT JOIN
(
    SELECT
        ctr_id ,
        DATE_TRUNC( 'month' , dt ) AS mth ,
        SUM( amt ) OVER ( PARTITION BY ctr_id ) AS pmts ,
        COUNT( * ) AS pmt_cnt
    FROM
        pmt
    GROUP BY
        ctr_id , DATE_TRUNC( 'month' , dt )
)
    AS pmt
        ON  pmt.ctr_id = sch.ctr_id
        AND pmt.mth = sch.mth
WINDOW
    ctr AS (
        PARTITION BY sch.ctr_id
        ORDER BY sch.mth
    )

The primary problem with the window function method is you need to refer back to the previous value of the same window function, but calculate the current value differently depending on that returned value. Any ideas how this would be solved? Would a recursive CTE be able to solve it?

EDIT:
Thank you Lennart for pointing out the missed use case. We must also be able to account for over-payments that would apply directly towards principal. For instance, always paying $5 extra every month or making a single $1000 payment. In either case, the same $145 is still due the next month as the loan would need to re-amortize for the scheduled payment amount to change. Given this requirement, we can't just sum all the payments and compare that to the scheduled payments due thus far.

Best Answer

After looking at custom aggregate functions, I realized that was the appropriate way to solve the issue. In case anyone else comes across this question and would like to see how I solved it:

CREATE OR REPLACE FUNCTION sfunc_outstanding_amount_with_carry
(
    previous_row    NUMERIC[]   , -- previous row output
    current_row     NUMERIC[]     -- current row input
)
RETURNS NUMERIC[]
AS $$
    DECLARE
        current_total       NUMERIC ;
        carried_amount      NUMERIC ;
        outstanding_amount  NUMERIC ;
        remainder_amount    NUMERIC ;
    BEGIN
        /*
            If the previous outstanding amount is zero, apply the least of the remainder of the
            previous row and the carryover towards the current value.
        */
        IF
            ( previous_row[1] = 0 )
        THEN
            carried_amount := LEAST( previous_row[2] , current_row[2] ) ;
        ELSE
            carried_amount := 0 ;
        END IF ;

        /*
            Calculate the current total and determine the remainder for the next row.
        */
        current_total := previous_row[1] + current_row[1] - carried_amount ;
        outstanding_amount := GREATEST( current_total , 0 ) ;
        remainder_amount := LEAST( current_total , 0 ) ;
        remainder_amount := CASE SIGN(remainder_amount) WHEN 0 THEN 0 ELSE ( remainder_amount / -1 ) END ;

        RETURN ARRAY[ outstanding_amount , remainder_amount ] ;
    END ;
$$ LANGUAGE PLPGSQL IMMUTABLE ;

CREATE OR REPLACE FUNCTION finalfunc_outstanding_amount_with_carry
(
    current_row NUMERIC[]
)
RETURNS NUMERIC
AS $$
    BEGIN
        RETURN current_row[1] ;
    END ;
$$ LANGUAGE PLPGSQL IMMUTABLE ;

CREATE AGGREGATE outstanding_amount_with_carry (NUMERIC[])
(
    SFUNC = sfunc_outstanding_amount_with_carry ,
    STYPE = NUMERIC[] ,
    FINALFUNC = finalfunc_outstanding_amount_with_carry ,
    INITCOND = '{0,0}'
) ;

Usage is as such:

SELECT
    sch.mth ,
    sch.ctr_id ,
    CASE
        WHEN LAG(pmt.pmt_cnt) OVER (ctr) > 1 AND pmt.pmts > sch.pmt_amt
        THEN pmt.pmts - sch.pmt_amt
        ELSE 0
    END AS carryover ,
    OUTSTANDING_AMOUNT_WITH_CARRY( ARRAY[
        sch.pmt_amt - pmt.pmts ,
        CASE
            WHEN LAG(pmt.pmt_cnt) OVER (ctr) > 1 AND pmt.pmts > sch.pmt_amt
            THEN pmt.pmts - sch.pmt_amt
            ELSE 0
        END 
    ] ) OVER ( ctr ) AS outstanding
FROM
    sch
LEFT JOIN
(
    SELECT
        ctr_id ,
        DATE_TRUNC( 'month' , dt ) AS mth ,
        SUM( amt ) OVER ( PARTITION BY ctr_id ) AS pmts ,
        COUNT( * ) AS pmt_cnt
    FROM
        pmt
    GROUP BY
        ctr_id , DATE_TRUNC( 'month' , dt )
)
    AS pmt
        ON  pmt.ctr_id = sch.ctr_id
        AND pmt.mth = sch.mth
WINDOW
    ctr AS (
        PARTITION BY sch.ctr_id
        ORDER BY sch.mth
    )

I found the explanation at https://hashrocket.com/blog/posts/custom-aggregates-in-postgresql very helpful when putting this solution together.

Related Question