Recipe - Largest delta between max peak actual demand and cited demand by meter, by year
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_uc_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_uc_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?
METER_ID | CALENDAR_YEAR | DELTA | UNIT_OF_MEASURE |
---|---|---|---|
1ED9C1E… | 2023 | 4.200000 | kW |
1ED9C1E… | 2023 | 0.000000 | kW |
Updated 8 months ago