skip to Main Content

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.

2

Answers


  1. 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

    Login or Signup to reply.
  2. This can be done in 3 steps:

    1. Calculate outstanding amount by due_date and payment amount by disbursed_date (cte1)
    2. Calculate cumulative outstanding amount by as of date (cte2)
    3. 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|
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search