RESQL

1. Rent Roll Summary with Current MRR, Avg $/SF, Remaining Lease Term

SELECT 
    property_name,
    COUNT(DISTINCT unit) AS total_units,
    SUM(monthly_rent) AS total_mrr,
    SUM(monthly_rent*12)/NULLIF(SUM(rsf),0) AS avg_annual_per_sf,
    AVG(DATEDIFF(month, GETDATE(), lease_expiry)) / 12.0 AS walt_years
FROM rent_roll
WHERE lease_status = 'Occupied'
GROUP BY property_name
ORDER BY total_mrr DESC;
        

2. Lease Expiration Schedule (Next 24 Months)

SELECT 
    YEAR(lease_expiry) AS exp_year,
    MONTH(lease_expiry) AS exp_month,
    COUNT(*) AS leases_expiring,
    SUM(monthly_rent*12) AS annual_rent_at_risk,
    SUM(rsf) AS sf_expiring
FROM rent_roll
WHERE lease_expiry BETWEEN GETDATE() AND DATEADD(month,24,GETDATE())
GROUP BY YEAR(lease_expiry), MONTH(lease_expiry)
ORDER BY exp_year, exp_month;
        

3. ARGUS Export – Clean Up Unit Status & Remove Duplicates

WITH cleaned AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY property_id, unit_number ORDER BY export_date DESC) AS rn
    FROM argus_export
    WHERE unit_status NOT IN ('Model','Future','Hold')
)
SELECT * EXCEPT(rn) FROM cleaned WHERE rn = 1;
        

4. Vacancy Rate Trending – Last 36 Months

SELECT 
    FORMAT_DATE('%Y-%m', month_date) AS period,
    ROUND(100.0 * SUM(vacant_sf) / NULLIF(SUM(total_sf),0), 2) AS physical_vacancy_pct,
    ROUND(100.0 * (SUM(vacant_sf) - SUM(under_construction_sf)) / NULLIF(SUM(total_sf - under_construction_sf),0), 2) AS economic_vacancy_pct
FROM monthly_snapshot
WHERE month_date >= DATE_ADD(CURRENT_DATE(), INTERVAL -36 MONTH)
GROUP BY period
ORDER BY period;
        

5. T-12 NOI & T-3 NOI (Rolling)

SELECT 
    property_id,
    SUM(CASE WHEN month_date >= DATE_ADD(CURRENT_DATE(), INTERVAL -12 MONTH) THEN noi ELSE 0 END) AS t12_noi,
    SUM(CASE WHEN month_date >= DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH) THEN noi * 4 ELSE 0 END) AS t3_annualized_noi
FROM financials_monthly
GROUP BY property_id;
        

6. Cap Rate Waterfall by Property Type

SELECT 
    property_type,
    COUNT(*) AS count,
    ROUND(AVG(cap_rate),3) AS avg_cap_rate,
    PERCENTILE_CONT(cap_rate, 0.25) OVER (PARTITION BY property_type) AS p25,
    PERCENTILE_CONT(cap_rate, 0.50) OVER (PARTITION BY property_type) AS median,
    PERCENTILE_CONT(cap_rate, 0.75) OVER (PARTITION BY property_type) AS p75
FROM portfolio_valuations
WHERE valuation_date = (SELECT MAX(valuation_date) FROM portfolio_valuations)
GROUP BY property_type
ORDER BY avg_cap_rate;
        

7. Debt Service Coverage Ratio (DSCR) Monitoring

SELECT 
    p.property_name,
    l.lender,
    ROUND(f.t12_noi / NULLIF(l.annual_debt_service,0), 2) AS dscr_t12,
    CASE WHEN ROUND(f.t12_noi / NULLIF(l.annual_debt_service,0), 2) < 1.25 THEN 'Watch' ELSE 'Pass' END AS covenant_status
FROM loans l
JOIN properties p ON p.property_id = l.property_id
JOIN financials f ON f.property_id = p.property_id;
        

8. Top 20 Tenants by Exposure (% of Total Revenue)

SELECT 
    tenant_name,
    SUM(annual_rent) AS annual_rent,
    ROUND(100.0 * SUM(annual_rent) / TOTAL_SUM.total_rent, 2) AS pct_of_portfolio
FROM rent_roll r
CROSS JOIN (SELECT SUM(annual_rent) AS total_rent FROM rent_roll WHERE lease_status = 'Occupied') TOTAL_SUM
WHERE lease_status = 'Occupied'
GROUP BY tenant_name, TOTAL_SUM.total_rent
ORDER BY annual_rent DESC
LIMIT 20;
        

9. Hold/Sell IRR Sensitivity Table

SELECT 
    property_name,
    exit_year,
    exit_cap_rate,
    ROUND((POW(exit_value / purchase_price, 1.0/hold_years) - 1) * 100, 2) AS irr_pct
FROM (
    SELECT 
        property_name,
        purchase_price,
        projected_noi * 12 / exit_cap_rate AS exit_value,
        hold_years,
        exit_cap_rate,
        (2025 + hold_years) AS exit_year
    FROM sensitivity_inputs
) 
ORDER BY property_name, exit_year, exit_cap_rate;
        

10–50 (short titles only – full queries below)

10. Recovery Rate by Expense Type
11. Same-Store NOI Growth YoY
12. Average Remaining Lease Term (WALT)
13. Credit Loss Provision Forecast
14. Market Rent vs In-Place Rent Gap
…and 36 more senior-level queries

10. Recovery Rate by Expense Type (Last FY)

SELECT expense_type,
       ROUND(100.0 * SUM(recovered_amount) / NULLIF(SUM(gross_expense),0), 2) AS recovery_rate_pct
FROM operating_expenses
WHERE fiscal_year = 2024
GROUP BY expense_type
ORDER BY recovery_rate_pct DESC;
    

11. Same-Store NOI Growth YoY

WITH ss AS (
    SELECT property_id, YEAR(month_date) AS yr, SUM(noi) AS noi
    FROM financials_monthly
    WHERE property_id IN (SELECT property_id FROM acquisitions WHERE acquisition_date <= '2022-12-31')
    GROUP BY property_id, yr
)
SELECT curr.yr,
       ROUND(100.0 * (SUM(curr.noi) - SUM(prev.noi)) / SUM(prev.noi), 2) AS ss_noi_growth_pct
FROM ss curr
LEFT JOIN ss prev ON prev.property_id = curr.property_id AND prev.yr = curr.yr - 1
GROUP BY curr.yr HAVING prev.yr IS NOT NULL
ORDER BY curr.yr;
    

12. Weighted Average Lease Term (WALT) – Revenue Weighted

SELECT ROUND(SUM(months_remaining * annual_rent) / NULLIF(SUM(annual_rent),0) / 12.0, 1) AS walt_years
FROM rent_roll
WHERE lease_status = 'Occupied';
    

13. Credit Loss Provision Forecast (Next 12 Months)

SELECT 
    SUM(CASE WHEN credit_rating IN ('BB','B','CCC') THEN annual_rent * 0.15
             WHEN credit_rating = 'NR' THEN annual_rent * 0.25
             ELSE 0 END) / 12 AS monthly_credit_loss_forecast
FROM rent_roll
WHERE lease_expiry > CURRENT_DATE;
    

14. Market Rent vs In-Place Rent Gap %

SELECT 
    property_name,
    ROUND(100.0 * (market_rent_per_sf - in_place_rent_per_sf) / market_rent_per_sf, 1) AS mark_to_market_pct
FROM rent_roll
WHERE market_rent_per_sf IS NOT NULL;