Building Energy Benchmarking

-- Step 1: Aggregate actual consumption from ODS-E timeseries
WITH actual AS (
    SELECT
        t.asset_id,
        SUM(t.kwh)                           AS annual_kwh,
        a.building_type,
        a.climate_zone,
        a.vintage,
        a.floor_area_sqm,
        a.state
    FROM odse_timeseries  t
    JOIN odse_assets       a ON a.asset_id = t.asset_id
    WHERE t.direction    = 'consumption'
      AND t.fuel_type    = 'electricity'
      AND t.timestamp   >= '2025-01-01'
      AND t.timestamp    < '2026-01-01'
    GROUP BY t.asset_id, a.building_type, a.climate_zone,
             a.vintage, a.floor_area_sqm, a.state
),

-- Step 2: Find the ComStock median EUI for matching building cohort
benchmark AS (
    SELECT
        "in.comstock_building_type"           AS building_type,
        "in.ashrae_iecc_climate_zone_2006"    AS climate_zone,
        "in.vintage"                          AS vintage,
        "in.state_abbreviation"               AS state,
        PERCENTILE_CONT(0.5) WITHIN GROUP (
            ORDER BY "out.site_energy_intensity_kbtu_per_sqft"
        )                                     AS median_eui_kbtu_sqft
    FROM comstock
    GROUP BY 1, 2, 3, 4
)

-- Step 3: Compare actual vs benchmark
SELECT
    ac.asset_id,
    ac.annual_kwh,
    ac.floor_area_sqm,
    (ac.annual_kwh * 3.412)
        / (ac.floor_area_sqm * 10.7639)      AS actual_eui_kbtu_sqft,
    bm.median_eui_kbtu_sqft,
    ROUND(
        ((ac.annual_kwh * 3.412) / (ac.floor_area_sqm * 10.7639))
        / NULLIF(bm.median_eui_kbtu_sqft, 0),
        2
    )                                         AS eui_ratio
FROM actual    ac
JOIN benchmark bm
  ON  ac.building_type = bm.building_type
  AND ac.climate_zone  = bm.climate_zone
  AND ac.vintage       = bm.vintage
  AND ac.state         = bm.state;