For lenders seeking to raise debt capital or acquire term sheets from capital providers, putting together a Loan Tape is usually the first step.
This SQL query is designed to generate a summary of outstanding balances, repayment rates and delinquency on each account, which you would then JOIN with information on customer characteristics. The latter could live inside your CRM or in Canopy (if set up correctly).
This query compiles data from various tables to provide valuable insights into account and payment-related metrics. The report is structured to assist with financial decision-making and monitoring, particularly in a lending or financial management context.
CUSTOMER_ID: This is the ID used to identify the account in your system.
STATUS: This field uses a CASE statement to determine the status of the account based on the outstanding amount (AMOUNT_OUTSTANDING). It assigns labels such as 'Paid in Full,' 'Delinquent,' 'Current,' or the account's actual status.
ACCOUNT_STATUS: This field represents the account's status.
ACCOUNT_SUBSTATUS: This field represents the substatus of the account
COHORT: Extract the month from the effective_at timestamp.
PAYMENT_FREQUENCY: This field represents the average payment frequency for the account.
DAYS_PAST_DUE: This field calculates the number of days past due for the account.
TOTAL_REVOLVING_FUNDS: This field calculates the total revolving funds
TOTAL_LATE_FEES_COLLECTED: This field calculates the total late fees collected.
MISSED_PAYMENTS: Number of missed payments.
CHARGE_OFF_DATE: The charge-off date for accounts that have been charged off.
CHARGE_OFF_AMOUNT: The charge-off amount for accounts that have been charged off.
ACCOUNT_ID: This field represents the unique identifier for the loan.
ADVANCE_DATE: Effective date of the loan, converted to a date format.
ORIGINAL_RECEIVABLE_BALANCE: Calculates the original receivable balance
AMOUNT_OUTSTANDING: Calculates the outstanding amount (principal)
REPAYMENT_START_DATE: Calculates repayment start date by selecting the minimum date between first_due_date from the loan_terms_statistics CTE and earliest_repayment from the loan_payment_statistics CTE.
LAST_REPAYMENT_DATE: Calculates the last repayment date by selecting the maximum date between last_due_date from the loan_terms_statistics CTE and latest_repayment from the loan_payment_statistics CTE.
TERM: Represents the total number of terms associated with the loan
REMAINING_TERM: Calculates the remaining terms of the loan
EXPECTED_PAYOFF_DATE: Expected payoff date of the loan
TOTAL_PRINCIPAL_COLLECTED: Calculates the total principal collected
TOTAL_PRINCIPAL_OFFSET: Calculates the total principal offset
TOTAL_FEE_COLLECTED: Calculates the total fee collected
TOTAL_FEE_OFFSET: Calculates the total fee offset
- The primary objective of this query is to create a detailed financial report encompassing various aspects of account and payment data.
- It calculates and presents critical financial metrics, such as delinquency status, charge-off dates, payment frequencies, and more.
- This report aids in assessing the financial health of accounts and tracking important financial events.
/* This payment_split_json_records CTE starts by extracting individual JSONB array elements from the line_item_relationships column in the line_items table. It's specifically interested in line items of types 'PAYMENT' or 'DEBIT_OFFSET' that have a status of 'VALID'. */ WITH payment_split_json_records AS ( select jsonb_array_elements(line_item_relationships) field, valid_at as effective_at, external_line_item_id as payment_external_line_item_id, account_id, line_item_type from data_direct.line_items where line_item_type IN ('PAYMENT', 'DEBIT_OFFSET') and line_item_status = 'VALID' ), /* This CTE transforms the JSONB array elements from the previous CTE into structured transaction data. It calculates several attributes, including original amount, transaction ID, paid-down client ID, type, parent external line item ID, and loan ID based on the type of line item. */ splits_as_transactions AS ( select 'PAYMENT_SPLIT' as line_item_type, payment_external_line_item_id, (psjr.field ->> 'split_amount_cents') :: int as original_amount_cents, (psjr.field ->> 'line_item_client_id') as transaction_id, (psjr.field ->> 'paid_down_line_item_client_id') as paid_down_client_id, (psjr.field ->> 'paid_down_line_item_type') as paid_down_type, ( psjr.field ->> 'paid_down_line_item_parent_client_id' ) as paid_down_parent_external_line_item_id, CASE (psjr.field ->> 'paid_down_line_item_type') WHEN 'AM_INTEREST' THEN ( psjr.field ->> 'paid_down_line_item_parent_client_id' ) WHEN 'LOAN' THEN (psjr.field ->> 'paid_down_line_item_client_id') END AS loan_id, effective_at, account_id, line_item_type AS original_payment_type from payment_split_json_records psjr ), /* This CTE filters and selects only those records with an original_payment_type of 'PAYMENT', effectively filtering out 'DEBIT_OFFSET' records. It focuses on payments made on accounts. */ collected_splits AS ( select * from splits_as_transactions where original_payment_type = 'PAYMENT' ), /* This CTE calculates statistics for debit offset splits. It computes the sum of the original_amount_cents for debit offset splits, grouping by account_id, loan_id, and paid_down_type. */ offset_splits_statistics AS ( select sum(original_amount_cents) offset_cents, loan_id, paid_down_type from splits_as_transactions where original_payment_type = 'DEBIT_OFFSET' group by account_id, loan_id, paid_down_type ), /* This CTE calculates statistics related to loan payments. It determines the latest and earliest repayment dates for loans, grouping by loan_id. */ loan_payment_statistics AS ( select loan_id, max(effective_at) latest_repayment, min(effective_at) earliest_repayment from collected_splits sat where loan_id is not null group by loan_id ), /* This CTE calculates statistics for late fee payments. It computes the sum of original_amount_cents for payments associated with late fees, grouping by account_id. */ late_fee_payment_statistics AS ( select account_id, SUM(original_amount_cents) collected from collected_splits where paid_down_type = 'MANUAL_FEE' group by account_id ), /* This CTE calculates statistics related to loan terms and payments. It calculates various metrics, including the number of missed payments, terms due, and others, for each loan. */ loan_terms_statistics AS ( select external_line_item_id as loan_id, min(cycle_exclusive_end) as first_exclusive_end_date, max(cycle_exclusive_end) as last_exclusive_end_date, max(min_pay_due_at) as last_due_date, min(min_pay_due_at) as first_due_date, sum( case when paid_on_time = false then 1 else 0 end ) missed_payments, sum( case when clock_timestamp() > min_pay_due_at then 1 else 0 end ) terms_due, count(1) total_terms_count from data_direct.am_forecast_public group by external_line_item_id ), /* This CTE calculates statistics related to account-level payments, including the count of payments made in each month. */ account_payment_statistics AS ( select account_id, count(1) AS payments_in_month, EXTRACT( MONTH FROM payments.effective_at ) from data_direct.line_items payments where payments.line_item_type = 'PAYMENT' AND payments.line_item_status = 'VALID' group by payments.account_id, EXTRACT( MONTH FROM payments.effective_at ) ), -- This CTE calculates the average monthly payment frequency for each account. account_monthly_payment_freq AS ( select account_id, avg(payments_in_month) avg_payment_in_month from account_payment_statistics group by account_id ), -- This CTE retrieve all available loan all_loans AS ( SELECT * FROM data_direct.line_items where line_item_type = 'LOAN' and line_item_status = 'VALID' ), -- This CTE retrieve all available accounts all_accounts AS ( SELECT * FROM data_direct.accounts ), /* This CTE combines various loan-related data to create a comprehensive view of each loan's status, terms, and collected amounts. */ loan_tapes AS ( select external_line_item_id AS LOAN_ID, account_id, effective_at :: date AS ADVANCE_DATE, original_amount_cents / 100.0 AS ORIGINAL_RECEIVABLE_BALANCE, principal_cents / 100.0 AS AMOUNT_OUTSTANDING, LEAST(lts.first_due_date, lps.earliest_repayment) :: date AS REPAYMENT_START_DATE, GREATEST(lts.last_due_date, lps.latest_repayment) :: date AS LAST_REPAYMENT_DATE, lts.total_terms_count AS TERM, lts.total_terms_count - lts.terms_due AS REMAINING_TERM, lts.last_due_date :: date AS EXPECTED_PAYOFF_DATE, ( loans.original_amount_cents - loans.principal_cents ) / 100.0 AS TOTAL_PRINCIPAL_COLLECTED, COALESCE(ABS(osls.offset_cents) / 100.0, 0) AS TOTAL_PRINCIPAL_OFFSET, loans.total_interest_paid_to_date_cents / 100.0 AS TOTAL_FEE_COLLECTED, COALESCE(ABS(osfs.offset_cents) / 100.0, 0) AS TOTAL_FEE_OFFSET from all_loans loans left join loan_payment_statistics lps ON lps.loan_id = loans.external_line_item_id left join loan_terms_statistics lts ON lts.loan_id = loans.external_line_item_id left join offset_splits_statistics osls ON osls.loan_id = loans.external_line_item_id and osls.paid_down_type = 'LOAN' left join offset_splits_statistics osfs ON osfs.loan_id = loans.external_line_item_id and osfs.paid_down_type = 'AM_INTEREST' ), /* This CTE calculates delinquency metrics, including the number of days past due, missed payments, and charge-off dates, based on the delinquency_report and statements tables. */ delinquency_metrics AS ( SELECT account_id, delinquent_as_of_date, dr.days_past_due, count(1) over (partition by account_id) missed_payments, rank() over ( partition by account_id order by effective_at desc ) ordering -- if the account has been delinquent for delinquent as of + 180 days, it should be charged off , CASE WHEN delinquent_as_of_date IS NOT NULL AND (delinquent_as_of_date + INTERVAL '180 days') <= clock_timestamp() THEN delinquent_as_of_date + INTERVAL '180 days' END AS charge_off_date FROM data_direct.delinquency_report dr JOIN data_direct.statements s using(account_id) WHERE dr.delinquent_as_of_date is not null and s.min_pay_due_at >= dr.delinquent_as_of_date AND s.min_pay_due_at <= clock_timestamp() ) SELECT aa.external_account_id AS CUSTOMER_ID, CASE WHEN lt.AMOUNT_OUTSTANDING = 0 THEN 'Paid in Full' WHEN aa.account_status_subtype = 'DELINQUENT' THEN 'Delinquent' WHEN aa.account_status = 'ACTIVE' THEN 'Current' else aa.account_status END AS STATUS, aa.account_status AS ACCOUNT_STATUS, aa.account_status_subtype AS ACCOUNT_SUBSTATUS, TO_CHAR(aa.effective_at, 'month') AS COHORT, ampf.avg_payment_in_month AS PAYMENT_FREQUENCY, COALESCE(dr.days_past_due, 0) AS DAYS_PAST_DUE, aa.credit_limit_cents / 100.0 AS TOTAL_REVOLVING_FUNDS, COALESCE(ABS(lfps.collected / 100.0), 0) AS TOTAL_LATE_FEES_COLLECTED -- using the delinquency report, when the account has a delinquent date, -- we can capture how many statements with due dates that have passed since the delinquent date -- to measure the number of missed payments , ( CASE WHEN dr.delinquent_as_of_date IS NULL THEN 0 ELSE dr.missed_payments END ) AS MISSED_PAYMENTS, ( CASE WHEN dr.charge_off_date IS NOT NULL AND aa.account_status_subtype = 'CHARGE_OFF' THEN dr.charge_off_date END ) AS CHARGE_OFF_DATE -- When the account the MCA belongs to has been charged off, then -- the outstanding MCA balance should be charged off , ( CASE WHEN dr.charge_off_date IS NOT NULL THEN lt.AMOUNT_OUTSTANDING END ) AS CHARGE_OFF_AMOUNT, lt.* FROM loan_tapes lt join all_accounts aa on aa.account_id = lt.account_id join account_monthly_payment_freq ampf on ampf.account_id = aa.account_id left join delinquency_metrics dr on dr.account_id = aa.account_id and dr.ordering = 1 left join late_fee_payment_statistics lfps on lfps.account_id = aa.account_id order by aa.external_account_id; -- REPAYMENT_START_DATE -- The earliest of two values. First payment made towards the Loan (MCA), -- or the due date of the first amortized due date of the Loan (MCA) -- LAST_REPAYMENT_DATE -- The latest of two values. Last payment made towards the Loan (MCA), -- or the due date of the last amortized due date of the Loan (MCA)
|0150fb79-1a8e-4fe0-92f1-a7e5ad6bb0d0||Paid in Full||CLOSED||PAID_OFF||april||1.00000000000000000000||0||10000.0000000000000000||0||0||can_319111||379b1d4d-2246-462a-b1f6-b6aed3f71428||2023-04-13||10000.0000000000000000||0.00000000000000000000||2023-04-14||2023-08-19||4||0||2023-08-19||10000.0000000000000000||0||0.00000000000000000000||0|
Updated 3 months ago