π Database Schema
Properties
PropertyID INT (PK)
PropertyName VARCHAR(255)
Address VARCHAR(500)
PurchasePrice DECIMAL(12,2)
UnitCount INT
MonthlyFinancials
FinancialID INT (PK)
PropertyID INT (FK)
ReportingMonth DATE
TotalIncome DECIMAL(12,2)
NOI DECIMAL(12,2)
Vacancy DECIMAL(5,2)
Tenants
TenantID INT (PK)
FirstName VARCHAR(100)
LastName VARCHAR(100)
Email VARCHAR(255)
LeaseEndDate DATE
π» Live SQL Queries
-- 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;
Click βRun Queryβ to execute the SQL statement...