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.


Fields

  • 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

Main goals

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

SQL Template

/*
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)

Sample Data

customer_idstatusaccount_statusaccount_substatuscohortpayment_frequencydays_past_duetotal_revolving_fundstotal_late_fees_collectedmissed_paymentscharge_off_datecharge_off_amountloan_idaccount_idadvance_dateoriginal_receivable_balanceamount_outstandingrepayment_start_datelast_repayment_datetermremaining_termexpected_payoff_datetotal_principal_collectedtotal_principal_offsettotal_fee_collectedtotal_fee_offset
0150fb79-1a8e-4fe0-92f1-a7e5ad6bb0d0Paid in FullCLOSEDPAID_OFFapril1.00000000000000000000010000.000000000000000000can_319111379b1d4d-2246-462a-b1f6-b6aed3f714282023-04-1310000.00000000000000000.000000000000000000002023-04-142023-08-19402023-08-1910000.000000000000000000.000000000000000000000
10f61e25-f3dd-46cd-90d8-c7bfbe648ea8CurrentACTIVEmarch1.000000000000000000000100000.00000000000000can_20220759c7ca8a-8637-4e25-85ba-02f9ef7140d22023-03-031000.00000000000000001000.00000000000000002023-04-262023-04-26102023-04-260.0000000000000000000000.000000000000000000000
1f2766cd-6c0e-492d-9ca0-53623663082fDelinquentSUSPENDEDDELINQUENTapril1.00000000000000000000362000.000000000000000002can_31998787b80965-7472-4cdb-a36c-c71947ab8baf2023-04-202000.00000000000000001816.67000000000000002023-06-142025-05-1424202025-05-14183.330000000000000000.000000000000000000000
2dea2490-9daf-4a82-8249-987b11ea6cbfCLOSEDCLOSEDCHARGE_OFFfebruary1.00000000000000000000153100000.00000000000002"can_20187573bb7776-91b6-40f9-8ff0-6e06a6084b1f2023-02-24400.0000000000000000400.00000000000000002023-03-192023-06-19402023-06-190.0000000000000000000000.000000000000000000000
2ea7e969-5ecf-4f50-9fdb-b8955507babcCurrentACTIVEmarch1.00000000000000000000176100000.00000000000001can_202214a9886d3f-db37-4d62-bca6-d929b6db14942023-03-0110000.000000000000000010000.00000000000000002023-03-272024-02-251262024-02-250.0000000000000000000000.000000000000000000000