Loan Tape

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