πŸ“Š 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...