Dormant Equity Reactivation for DSCR Cash-Out Refi

SELECT
    COUNT(*) AS INVESTOR_CNT,
    MEDIAN(ACTIVE_PORTFOLIO_VALUE) AS MEDIAN_PORTFOLIO_VALUE,
    MEDIAN(PORTFOLIO_EQUITY_VALUE) AS MEDIAN_PORTFOLIO_EQUITY_VALUE,
    MEDIAN(PORTFOLIO_LTV_PCT) AS MEDIAN_PORTFOLIO_LTV_PCT,
    MEDIAN(TOTAL_PROPERTIES_OWNED) AS MEDIAN_PROPERTIES_OWNED,
    MEDIAN(SFH_SHARE_PCT + MFH_2_4_SHARE_PCT) AS MEDIAN_RESIDENTIAL_SHARE_PCT,
    SUM(PORTFOLIO_EQUITY_VALUE) AS TOTAL_MODELED_PORTFOLIO_EQUITY_VALUE
FROM <YOUR_DATABASE>.<YOUR_SCHEMA>.DORMANT_EQUITY_REACTIVATION;

SELECT
    INVESTOR_ID,
    INVESTOR_NAME,
    INVESTOR_TYPE_CODE,
    MAILING_CITY,
    MAILING_STATE,
    MAILING_ZIP,
    ACTIVE_PORTFOLIO_VALUE,
    PORTFOLIO_EQUITY_VALUE,
    PORTFOLIO_LTV_PCT,
    TOTAL_PROPERTIES_OWNED,
    AVERAGE_PROPERTY_VALUE,
    SFH_HOLDINGS_CNT,
    MFH_2_4_HOLDINGS_CNT,
    SFH_SHARE_PCT,
    MFH_2_4_SHARE_PCT,
    STRATEGY_INDEX_CODE,
    CAPITAL_REINVESTMENT_READINESS_CODE,
    CAPITAL_REINVESTMENT_READINESS_SCORE,
    DISTRESS_EXPOSURE_INDEX_CODE,
    EXIT_PROPENSITY_INDEX_CODE
FROM <YOUR_DATABASE>.<YOUR_SCHEMA>.DORMANT_EQUITY_REACTIVATION
WHERE PORTFOLIO_EQUITY_VALUE >= 1000000
  AND PORTFOLIO_LTV_PCT <= 35
  AND TOTAL_PROPERTIES_OWNED >= 5
  AND DISTRESS_EXPOSURE_INDEX_CODE IN ('D', 'E')
  AND EXIT_PROPENSITY_INDEX_CODE NOT IN ('A', 'B')
ORDER BY
    PORTFOLIO_EQUITY_VALUE DESC,
    ACTIVE_PORTFOLIO_VALUE DESC,
    CAPITAL_REINVESTMENT_READINESS_SCORE DESC NULLS LAST
LIMIT 500;

WITH TIERED_INVESTORS AS (
    SELECT
        INVESTOR_ID,
        INVESTOR_NAME,
        INVESTOR_TYPE_CODE,
        MAILING_CITY,
        MAILING_STATE,
        MAILING_ZIP,
        ACTIVE_PORTFOLIO_VALUE,
        PORTFOLIO_EQUITY_VALUE,
        PORTFOLIO_LTV_PCT,
        TOTAL_PROPERTIES_OWNED,
        CAPITAL_REINVESTMENT_READINESS_CODE,
        DISTRESS_EXPOSURE_INDEX_CODE,
        EXIT_PROPENSITY_INDEX_CODE,
        CASE
            WHEN PORTFOLIO_EQUITY_VALUE >= 2500000
             AND PORTFOLIO_LTV_PCT <= 35
             AND TOTAL_PROPERTIES_OWNED >= 10
                THEN 'TIER_1_ENTERPRISE_EQUITY'
            WHEN PORTFOLIO_EQUITY_VALUE >= 1000000
             AND PORTFOLIO_LTV_PCT <= 50
             AND TOTAL_PROPERTIES_OWNED >= 5
                THEN 'TIER_2_HIGH_EQUITY'
            WHEN PORTFOLIO_EQUITY_VALUE >= 500000
             AND PORTFOLIO_LTV_PCT <= 50
             AND TOTAL_PROPERTIES_OWNED >= 3
                THEN 'TIER_3_CORE_COHORT'
            ELSE 'REVIEW'
        END AS CASH_OUT_PRIORITY_TIER
    FROM <YOUR_DATABASE>.<YOUR_SCHEMA>.DORMANT_EQUITY_REACTIVATION
    WHERE DISTRESS_EXPOSURE_INDEX_CODE IN ('D', 'E')
      AND EXIT_PROPENSITY_INDEX_CODE NOT IN ('A', 'B')
)
SELECT
    CASH_OUT_PRIORITY_TIER,
    COUNT(*) AS INVESTOR_CNT,
    MEDIAN(PORTFOLIO_EQUITY_VALUE) AS MEDIAN_PORTFOLIO_EQUITY_VALUE,
    MEDIAN(PORTFOLIO_LTV_PCT) AS MEDIAN_PORTFOLIO_LTV_PCT,
    MEDIAN(TOTAL_PROPERTIES_OWNED) AS MEDIAN_PROPERTIES_OWNED,
    SUM(PORTFOLIO_EQUITY_VALUE) AS TOTAL_MODELED_PORTFOLIO_EQUITY_VALUE
FROM TIERED_INVESTORS
GROUP BY CASH_OUT_PRIORITY_TIER
ORDER BY
    CASE CASH_OUT_PRIORITY_TIER
        WHEN 'TIER_1_ENTERPRISE_EQUITY' THEN 1
        WHEN 'TIER_2_HIGH_EQUITY' THEN 2
        WHEN 'TIER_3_CORE_COHORT' THEN 3
        ELSE 4
    END;

WITH BORROWER_BOOK AS (
    SELECT
        BORROWER_ID,
        BORROWER_NAME,
        MAILING_ZIP
    FROM <YOUR_DATABASE>.<YOUR_SCHEMA>.<YOUR_BORROWER_TABLE>
),
MATCHED_BORROWERS AS (
    SELECT
        b.BORROWER_ID,
        b.BORROWER_NAME,
        d.INVESTOR_ID,
        d.INVESTOR_NAME,
        d.INVESTOR_TYPE_CODE,
        d.MAILING_CITY,
        d.MAILING_STATE,
        d.MAILING_ZIP,
        d.ACTIVE_PORTFOLIO_VALUE,
        d.PORTFOLIO_EQUITY_VALUE,
        d.PORTFOLIO_LTV_PCT,
        d.TOTAL_PROPERTIES_OWNED,
        d.CAPITAL_REINVESTMENT_READINESS_CODE,
        d.CAPITAL_REINVESTMENT_READINESS_SCORE,
        d.DISTRESS_EXPOSURE_INDEX_CODE,
        d.EXIT_PROPENSITY_INDEX_CODE,
        d.QUALIFIED_MULTI_PRODUCT_LENDING_FLAG,
        d.QUALIFIED_BRIDGE_REHAB_TAKEOUT_FLAG,
        d.QUALIFIED_FORWARD_DEMAND_FLAG,
        d.QUALIFIED_PARKED_CAPITAL_FLAG
    FROM BORROWER_BOOK b
    JOIN <YOUR_DATABASE>.<YOUR_SCHEMA>.DORMANT_EQUITY_REACTIVATION d
        ON UPPER(TRIM(b.BORROWER_NAME)) = UPPER(TRIM(d.INVESTOR_NAME))
       AND b.MAILING_ZIP = d.MAILING_ZIP
)
SELECT
    *
FROM MATCHED_BORROWERS
ORDER BY
    PORTFOLIO_EQUITY_VALUE DESC,
    CAPITAL_REINVESTMENT_READINESS_SCORE DESC NULLS LAST;

SELECT
    QUALIFIED_MULTI_PRODUCT_LENDING_FLAG,
    QUALIFIED_BRIDGE_REHAB_TAKEOUT_FLAG,
    QUALIFIED_FORWARD_DEMAND_FLAG,
    QUALIFIED_PARKED_CAPITAL_FLAG,
    COUNT(*) AS INVESTOR_CNT,
    MEDIAN(PORTFOLIO_EQUITY_VALUE) AS MEDIAN_PORTFOLIO_EQUITY_VALUE,
    MEDIAN(PORTFOLIO_LTV_PCT) AS MEDIAN_PORTFOLIO_LTV_PCT,
    MEDIAN(TOTAL_PROPERTIES_OWNED) AS MEDIAN_PROPERTIES_OWNED,
    MEDIAN(ACTIVE_PORTFOLIO_VALUE) AS MEDIAN_PORTFOLIO_VALUE
FROM <YOUR_DATABASE>.<YOUR_SCHEMA>.DORMANT_EQUITY_REACTIVATION
GROUP BY
    QUALIFIED_MULTI_PRODUCT_LENDING_FLAG,
    QUALIFIED_BRIDGE_REHAB_TAKEOUT_FLAG,
    QUALIFIED_FORWARD_DEMAND_FLAG,
    QUALIFIED_PARKED_CAPITAL_FLAG
ORDER BY
    INVESTOR_CNT DESC;