-- Portfolio Performance Analysis with Window Functions
WITH PropertyMetrics AS (
    SELECT 
        p.PropertyName,
        p.PurchasePrice,
        SUM(mf.TotalIncome) as TotalRevenue,
        SUM(mf.NOI) as TotalNOI,
        AVG(mf.Vacancy) as AvgVacancy,
        ROW_NUMBER() OVER (ORDER BY SUM(mf.NOI) DESC) as NOI_Rank,
        ROUND(SUM(mf.NOI) / NULLIF(p.PurchasePrice, 0) * 100, 2) as CapRate
    FROM Properties p
    LEFT JOIN MonthlyFinancials mf ON p.PropertyID = mf.PropertyID
    WHERE mf.ReportingMonth >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY p.PropertyID, p.PropertyName, p.PurchasePrice
)
SELECT 
    PropertyName,
    FORMAT(PurchasePrice, 'C') as PurchasePrice,
    FORMAT(TotalRevenue, 'C') as AnnualRevenue,
    FORMAT(TotalNOI, 'C') as AnnualNOI,
    CONCAT(ROUND(AvgVacancy, 1), '%') as VacancyRate,
    CONCAT(CapRate, '%') as CapRate,
    NOI_Rank as Performance_Rank
FROM PropertyMetrics
ORDER BY NOI_Rank;