Cumulative Sum of Repayments to calculate outstanding as on date in MySQL

iamawesome
November 13, 2022
268 views
0 votes
2 Answers

I have a repayment table that looks like this below,

cus_id	due_date	principal	disbursed_date
1	01-01-2022	10	01-11-2021
1	01-02-2022	10	01-11-2021
1	01-03-2022	10	01-11-2021
2	15-03-2022	20	15-02-2022
1	01-04-2022	10	01-11-2021
3	01-04-2022	15	20-03-2022
2	15-04-2022	20	15-02-2022
3	01-05-2022	15	20-03-2022
2	30-05-2022	20	15-02-2022
1	30-05-2022	10	01-11-2021
3	01-06-2022	15	20-03-2022
2	15-06-2022	20	15-02-2022
2	30-06-2022	20	15-02-2022
3	01-07-2022	55	20-03-2022

One can pay any amount on any day of the month, there can also be 2 payments by the same customer within one month.
disbursed_day is the day of disbursement (could be any day before 1st EMI) and is the same for each cus_id. The total amount for each customer is the sum(principal) group by cus_id i.e. for customers 1, 2, and 3 total amount is respectively 50, 100, and 100.

I want to calculate the outstanding as of each due_date. My expected results would look like below,

| date_as_of   | outstanding |                                            |
| ------------ | ----------- | ------------------------------------------ |
| 01-01-2022   | 40          |  -- total outstanding as on 50, paid 10    |
| 01-02-2022   | 30          |  -- cus1 paid emi 10                       |
| 01-03-2022   | 120         |  -- amt for 2 disbursed on 15-02, 20+100   |
| 15-03-2022   | 100         |  -- cus2 paid emi of 20                    |
| 01-04-2022   | 175         |  -- amt for 3 disbursed on 20-03, 10+80+85 |
| 15-04-2022   | 155         |  -- cus2 paid emi of 20                    |
| 01-05-2022   | 140         |  -- cus3 paid emi of 15                    |
| 30-05-2022   | 110         |                                            |
| 01-06-2022   | 95          |                                            |
| 15-06-2022   | 75          |                                            |
| 30-06-2022   | 55          |                                            |
| 01-07-2022   | 0           |                                            |

For 1st Feb EMI, cus1 paid 2 EMI of 10, so outstanding as of 1st Feb will be 50-(10+10) = 30.

For 1st Mar EMI, cus1 paid 3 EMI of 10. Cus2 disbursed amt of 100 on 15th of Feb, so outstanding as of 1st Mar will be (50-(10+10+10))+100 = 120

On 15th Mar, cus 2 paid EMI of 20, so outstanding is (50-(10+10+10))+ (100-20) = 100

For 1st Apr EMI, cus1 paid 4 EMI of 10. Cus 2 paid 1 EMI of 20 (on 15th of March). Amt for cus3 disbursed on 20th March, but also had paid EMI of 15. So outstanding will be (50-(10+10+10+10)) + (100-20) + (100-15) = 175

This is how the calculation of outstanding should be. I was trying this approach below,

select *, (osp_as_on - principal) balance from (
    select due_date, principal, sum(net_repayment) over(order by due_date desc) osp_as_on from (
        select due_date, principal, sum(principal) net_repayment
            from repayments
        group by 1
    ) t1 
) t2 order by 1;

But my approach isn’t correct as my query isn’t considering the disbursed_date, as only after the disbursement date, I’ve to consider the remaining balance from the total to calculate the correct outstanding as of due date.

Any help from the community would be greatly appreciated. I’m using MySQL8.0.

Answers

One approach unpivots the columns to rows, so we can properly compute the window sum at any point in time, and then filters out unrelevant rows (ie those that correspond to "disbursed dates").

select date_as_of, outstanding
from (
    select x.*
        sum(x.outstanding) over(order by x.date_as_of) outstanding
    from mytable t
    cross join lateral (
        select t.disbursed_date as date_as_of, t.principal as outstanding, 0 keep_row
        union all select t.due_date, - t.principal, 1
    ) x
) t
where keep_row
order by date_as_of

For your sample data, this returns:

date_as_of	outstanding
2022-01-01	40
2022-02-01	30
2022-03-01	120
2022-03-15	100
2022-04-01	175
2022-04-01	175
2022-04-15	155
2022-05-01	140
2022-05-30	110
2022-05-30	110
2022-06-01	95
2022-06-15	75
2022-06-30	55
2022-07-01	0

Demo on DB Fiddle

This can be done in 3 steps:

Calculate outstanding amount by due_date and payment amount by disbursed_date (cte1)
Calculate cumulative outstanding amount by as of date (cte2)
Filter out rows not to be reported.

with cte1 as (
select disbursed_date  as date_as_of,
       sum(principal)  as outstanding,
       false           as report_flag
  from mytable
 group by 1
 union all 
select due_date        as date_as_of,
       sum(-principal) as outstanding,
       true            as report_flag
  from mytable
 group by 1),
cte2 as (
select date_as_of,
       sum(outstanding) over (order by date_as_of) as outstanding,
       report_flag
  from cte1)
select date_as_of,
       outstanding
  from cte2
 where report_flag
 order by 1;

Result:

date_as_of|outstanding|
----------+-----------+
2022-01-01|         40|
2022-02-01|         30|
2022-03-01|        120|
2022-03-15|        100|
2022-04-01|        175|
2022-04-15|        155|
2022-05-01|        140|
2022-05-30|        110|
2022-06-01|         95|
2022-06-15|         75|
2022-06-30|         55|
2022-07-01|          0|

Please signup or login to give your own answer.

Click here to cancel reply.