Loans Overview
Generate a comprehensive report for your portfolio of Loans. It aggregates various metrics related to account balances, fees, and payments, providing valuable insights into each account's financial status. The query combines multiple common table expressions to calculate and organize these metrics efficiently.
Note:
This template is most useful for an instalment loan programme. The query is written with the assumption that each Account is equivalent to a single instalment loan.
For Revolving and MultiLoan products, you would have to re-write this SQL query.
Fields
Loan ID: This is the ID used to identify the account in your system.Status: The Status of the Account. Active upon account creation.Status Subtype: The subtype of the Status of the Account. Null upon account creation.Original Principal Balance: The initial principal balance associated with the account.Current Principal Balance: Current total principal balance associated with the account.Original Fee Balance: The initial fee balance associated with the account.Current Fees Balance: The total fee balance associated with the account.Total Balance: The total balance associated with the account. This balance is comprised of the sum of the following six balance items.Last Payment Amount: Last payment amountLast Payment Date: Last payment date
SQL Template
WITH total_balance_cents AS ( -- Select getting the total balance for each account
SELECT
li.account_id,
sum(balance_cents) AS total_balance_cents
FROM
data_direct.line_items li
WHERE
li.line_item_status IN ('PENDING', 'VALID')
GROUP BY
li.account_id
),
principal_balance AS ( -- Select getting the total principal balance for each account
SELECT
li.account_id,
sum(principal_cents) AS principal_cents
FROM
data_direct.line_items li
WHERE
li.line_item_status = 'VALID'
AND (
li.line_item_type IN ('PAYMENT_REVERSAL_FEE')
OR li.line_item_type NOT IN (
'YEAR_FEE',
'MONTH_FEE',
'RETURN_CHECK_FEE',
'LATE_FEE',
'FEE',
'MANUAL_FEE',
'CREDIT_OFFSET_FEE',
'PAYMENT_REVERSAL_FEE',
'FEE_FOR_AM',
'ORIG_FEE_FOR_AM',
'AM_FEE',
'RECURRING_FEE'
)
)
GROUP BY
li.account_id
),
fees_balance as( -- Select getting the total fees balance for each account
SELECT
li.account_id,
sum(balance_cents) AS fees_balance_cents
FROM
data_direct.line_items AS li
WHERE
li.line_item_status = 'VALID'
AND li.line_item_type IN (
'YEAR_FEE',
'MONTH_FEE',
'RETURN_CHECK_FEE',
'LATE_FEE',
'FEE',
'MANUAL_FEE',
'CREDIT_OFFSET_FEE',
'PAYMENT_REVERSAL_FEE',
'FEE_FOR_AM',
'ORIG_FEE_FOR_AM',
'AM_FEE',
'RECURRING_FEE'
)
GROUP BY
li.account_id
),
last_payment AS ( -- Select getting the last payment amount and date for each account
SELECT
a .account_id,
li2.effective_at::date AS last_payment_date,
li2.original_amount_cents AS last_payment_amount
FROM
data_direct.accounts a
LEFT JOIN (
SELECT
account_id,
max(created_at) AS last_line_item_timestamp
FROM
data_direct.line_items
WHERE
line_item_type = 'PAYMENT'
GROUP BY
account_id
) li ON li.account_id = a .account_id
LEFT JOIN data_direct.line_items li2 ON li2.account_id = li.account_id
and li2.created_at = li.last_line_item_timestamp
),
original_principal AS ( -- Select getting the original total principal for each account
SELECT
account_id,
li.original_amount_cents AS orig_principal_amount
FROM
data_direct.line_items li
WHERE
li.line_item_type IN ('LOAN')
),
original_fee AS ( -- Select getting the original total fees for each account
SELECT
account_id,
li.original_amount_cents AS orig_fee_amount
FROM
data_direct.line_items li
WHERE
li.line_item_type IN ('ORIG_FEE_FOR_AM')
)
SELECT
a .external_account_id AS "Loan ID",
a .account_status,
a .account_status_subtype,
ROUND(op.orig_principal_amount / 100 ::NUMERIC, 2) AS "Original Principal Balance",
ROUND(pb.principal_cents / 100 ::NUMERIC, 2) AS "Current Principal Balance",
ROUND(ofee.orig_fee_amount / 100 ::NUMERIC, 2) AS "Original Fee Balance",
ROUND(fb.fees_balance_cents / 100 ::NUMERIC, 2) AS "Current Fees Balance",
ROUND(tb.total_balance_cents / 100 ::NUMERIC, 2) AS "Total Balance",
ROUND(lp.last_payment_amount / 100 ::NUMERIC, 2) AS "Last Payment Amount",
lp.last_payment_date AS "Last Payment Date"
FROM
data_direct.accounts a
LEFT JOIN total_balance_cents tb USING(account_id)
LEFT JOIN principal_balance pb USING(account_id)
LEFT JOIN fees_balance fb USING(account_id)
LEFT JOIN last_payment lp USING(account_id)
LEFT JOIN original_principal op USING(account_id)
LEFT JOIN original_fee ofee USING(account_id)
WHERE
a .account_status = 'ACTIVE'
OR a .account_status_subtype = 'PAID_OFF'
Sample Data
| Loan ID | account_status | account_status_subtype | Original Principal Balance | Current Principal Balance | Original Fee Balance | Current Fees Balance | Total Balance | Last Payment Amount | Last Payment Date |
|---|---|---|---|---|---|---|---|---|---|
| 10a303e0-d9d2-4311-bf60-daa39648284f | CLOSED | PAID_OFF | 1500.00 | 0.00 | 0.00 | 0.00 | -257.21 | 2021-11-16 | |
| 1dc185a8-82ee-495e-bea0-00df8a4a4c7d | ACTIVE | ||||||||
| 6220f577-6267-4e8e-a935-83839d4098ff | CLOSED | PAID_OFF | 452.52 | 0.00 | 0.00 | 0.00 | -77.58 | 2021-11-16 | |
| c105a0f1-ef77-4489-9c60-34b430567576 | CLOSED | PAID_OFF | 1500.00 | 0.00 | 0.00 | -246.57 | 2023-10-20 | ||
| 6bbdb1ac-c62e-423d-986b-1046892a2276 | CLOSED | PAID_OFF | 1500.00 | 0.00 | 0.00 | 0.00 | -257.21 | 2021-11-16 | |
| 71efc27f-2c00-4280-8fae-b47a8fad5217 | ACTIVE | 500.00 | 500.00 | ||||||
| 32df9a92-7a8f-4a5d-886d-85df4e0fd83e | CLOSED | PAID_OFF | 1500.00 | 0.00 | 0.00 | 0.00 | -269.31 | 2021-12-16 | |
| f14d0f07-17af-4c81-a0bf-912b56988841 | CLOSED | PAID_OFF | 50000.00 | 0.00 | 1570.00 | 0.00 | 0.00 | -12892.50 | 2023-12-21 |
| can_819 | ACTIVE | 0.00 | 0.00 | 0.00 | |||||
| f1d60701-fe87-4cb2-8d69-084e1a129206 | CLOSED | PAID_OFF | 453.10 | 0.00 | 0.00 | 0.00 | -40.04 | 2024-06-24 |
Updated 3 months ago