General Explanation

What is this recipe?

This recipe calculates the difference (delta) between the measured max peak demand and the cited max peak demand for each meter, by year. (Measured demand is the value actually read from a meter. Cited demand is the value the provider has just cited on the bill.)

Use case

What is this recipe for?

This recipe is useful to users that want to compare their actual usage with their cited usage by meter and see the largest discrepancies.

Snowflake SQL

What’s the snowflake SQL for this recipe?
What does the SQL for this recipe mean?

with measured_cited_deltas as (
    select meter_id
        , year(period_end_date) as calendar_year
        , standardized_measured_max_demand_usage_value
        , standardized_cited_max_demand_usage_value
        , standardized_measured_max_demand_usage_unit_of_measure as uom
        , abs(standardized_measured_max_demand_usage_value - standardized_cited_max_demand_usage_value) as delta
    from <YOUR_SNOWFLAKE_SHARE>.datahub_meters_by_statement_current 
    where standardized_measured_max_demand_usage_unit_of_measure = standardized_cited_max_demand_usage_unit_of_measure
)

select meter_id
    , calendar_year
    , max(delta) as max_delta
    , any_value(uom) as unit_of_measure
from measured_cited_deltas
group by meter_id, calendar_year
order by max_delta desc;

This query uses a CTE (”Common Table Expression”) to break down the logic. The CTE gathers the raw data and does the delta calculation, and then the final query applies the max aggregation function to the CTE’s results.

The CTE is named measured_cited_deltas and it looks at each row in the datahub_meters_by_statement_current table. For each row, it calculates the absolute difference between the measured and cited demand.

The final select uses the results of this CTE and applies the max aggregation function, grouping by the meter_id and the calendar_year.

The result is a delta value per meter per year.

Return format

What’s the return format and an explanation of each returned field?