3

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.

1

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.

0

This is what I intended to say in my comment:

select ctr_id, mth, cumulative_pmt_amt
     , coalesce(cumulative_amt, 0) as cumulative_amt
     , coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance
from (     
    select ctr_id, mth
         , sum(pmt_amt) over (partition by ctr_id 
                              order by mth) as cumulative_pmt_amt
         , ( select sum(amt) 
             from pmt y 
             where x.ctr_id = y.ctr_id 
               and y.dt <= x.mth ) as cumulative_amt 
    from sch x
) as t;

For a negative balance, you can use a case to map that to 0.

Fiddle

Excellent first post BTW

  • Thanks for pointing out something I missed in my question, you can prepay your loan. You can pay $150 every month and always apply a little extra to principal. Alternatively, if you make a single $1000 payment that covers your $145 due for the month, but the remaining $855 has gone directly towards principal. As such, you still owe $145 for the next month. – TH58PZ700U Nov 19 at 15:24
  • In regards to your answer, I'm actually doing something fairly similar right now in my far more complicated real life example. Instead of getting a payment sum total for lifetime to date at each month, I sum the payments for each month, then use a window function to keep a running delta for each month. By leveraging Evan's answer here: dba.stackexchange.com/a/179368 I can track only underpayments to know how much additional is required next month. But I can't seem to factor that into the two payments in a month problem. – TH58PZ700U Nov 19 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.