Loans Overview

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'