Evolution of overdues

Hi,

Despite my efforts I cannot think of a way to answer my need :

I have 2 tables containing respectively a set of loans to be reimbursed, and a set of reimbursements on these loans (not all loans have an entry in reimbursement table because some of them remain unpaid).

Table loan has columns:

  • id
  • amount
  • due_date

Table reimbursement has columns:

  • id
  • debt_id
  • payment_date

My goal is to obtain the amount that was overdue for more than 5 days for each date in the past.

Here is where I am at (in Postgres grammar):

with due_for_5_days_by_date AS (
	select 
  		due_date + interval '5' DAY as due_date,
  		sum(amount) as amount
  	FROM loan
  	GROUP BY due_date
),
paid_by_date as (
	select 
  		payment_date,
  		sum(amount) as amount
  	FROM reimbursement
  	join loan on loan.id = loan_id
  	GROUP BY payment_date
),
cumulated as (
	SELECT
  		COALESCE(due_date, payment_date) as date,
  		SUM(COALESCE(paid_by_date.amount, 0)) over (order by COALESCE(due_date, payment_date)) as paid,
  		SUM(COALESCE(due_for_5_days_by_date.amount, 0)) over (order by COALESCE(due_date, payment_date)) as due_5d
  	FROM due_for_5_days_by_date
  	FULL OUTER JOIN paid_by_date on due_date = payment_date
)
select 
	DATE,
    due_5d - paid as overdue_5d
from cumulated
order by date

Explanation :

  1. First I get the amount due for exactly 5 days for each date
  2. Then, I get the amount paid for each date
  3. Then I sum the amounts obtained at 1. and 2. to get the cumulative due and paid amounts
  4. Then by difference I get the overdue amount

But the problem is that, by doing this, the amount paid by date also contains reimbursement of loans expired for less than 5 days. In other words the paid amount for each date in the final query is overestimated as it should exclude the amount received on loans recently expired on that date. I cannot think of a way to fix this.

Can you think of a way to obtain what I want ?

Thanks a lot for your help !

Is this the type of loan where you repay in more than one installment? (eg., you might take out loan for a car for $10,000 and pay it over a period of 3 years in monthly installments). If that is the case, your tables don't have enough data to represent such a loan and the repayment schedule.

So, in the following, I am going to assume that this is a loan which you repay in just one installment (e.g., you borrowed $10,000 and 3 months later, you pay it back with interest perhaps). Even in that case, you need to account for partial payments etc., don't you? In that case, you could do something like shown below:

SELECT
    L.Id,
    L.Amount,
    L.Due_Date,
    R.Payment_Date,
    IsLate = 
        CASE
            WHEN DATEDIFF(DAY,L.Due_Date,COALESCE(R.Payment_date,'20991231')) > 5 THEN 'True'
            ELSE 'False'
        END 
FROM
    dbo.Loan AS L
    LEFT JOIN dbo.Reimbursement as R ON
        R.Id = L.Id;

Hi @JamesK ,
Yes I simplified the problem to focus on my core issue.
Your query just gives me whether a loan was late or not. What I need is the total overdue amount at any point in time.

I think I found a way to do it, but it uses an expensive cross join, so I am still looking for a better solution:

with all_dates as (
    select distinct payment_date as date
    from reimbursement
    union
    select distinct due_date as date
    from loan
    union
    select distinct due_date + interval '5' day as date
    from loan
), 
joined_data as (
	SELECT
  		amount,
  		due_date,
  		payment_date
 	from loan
  	LEFT join reimbursement on loan.id = loan_id
)
select 
	date,
    SUM(
      CASE
      	WHEN (due_date <= DATE - INTERVAL '5' day) and (payment_date > DATE or payment_date is null) then amount
     	else 0
      END
    ) as overdue_5d
FROM all_dates
cross join joined_data
group by date
order by DATE

I don't know Postgres SQL syntax, but from Google, it looks like this should get the individual loan overdue amounts. If that looks correct, then you'd just need to add an outer query to sum them up.

I didn't know how to get current date in Postgres, so I just ?...? to indicate that.

SELECT l.id, l.amount, r.amount_paid, l.amount - r.amount_paid amount_overdue
FROM loan l
LEFT JOIN LATERAL (
    SELECT SUM(amount) AS amount_paid
    FROM reimbursement r
    WHERE r.debt_id = l.id AND r.payment_date <= l.due_date + interval '5' DAY
) r ON r.amount_paid < l.amount
WHERE l.due_date + interval '5' DAY < {?todays_date?}