General Explanation

What is this recipe?

This is a recipe to find your highest on peak charge grouped by site ID.

Use case

What is this recipe for?

This recipe is for users who want to see what sites they have are generating the highest on peak demand charges which means that the sites are drawing a lot of usage within a short period of time. If the service type is electric, these sites could benefit from a solar and storage installation depending on their location and the likelihood that they may continue to experience sustained high on peak demand charges.

Snowflake SQL

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

-- by site_id for the 2023 calendar year
select site_id, round(max(standardized_measured_max_demand_usage_value), 2) as max_demand_value, any_value(standardized_measured_max_demand_usage_unit_of_measure) as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where year(period_end_date) = '2023'
    and year(period_start_date) = '2023'
    and standardized_measured_max_demand_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by site_id;

This query finds the maximum demand usage value by service type per site for the 2023 calendar year. It returns the site, the max demand value, the units the value is in, and the service type.

If you have not completely mapped your meters to sites, you can also group by meter id like so:

-- by meter_id for the 2023 calendar year
select meter_id, round(max(standardized_measured_max_demand_usage_value), 2) as max_demand_value, any_value(standardized_measured_max_demand_usage_unit_of_measure) as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where year(period_end_date) = '2023'
    and year(period_start_date) = '2023'
    and standardized_measured_max_demand_usage_value is not null
    and meter_id is not null
  group by meter_id, service_type
  order by meter_id;

You also may want to order by the max_demand_value to identify your sites that have the highest demand spikes:

-- by site_id for the 2023 calendar year, ordered by max_demand_value descending
select site_id, round(max(standardized_measured_max_demand_usage_value), 2) as max_demand_value, any_value(standardized_measured_max_demand_usage_unit_of_measure) as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where year(period_end_date) = '2023'
    and year(period_start_date) = '2023'
    and standardized_measured_max_demand_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by max_demand_value desc;

You may also want to find your max_demand_values across all of your data in DataHub which you can do by removing the period_end_date part of the where clause like so:

-- by site_id across all DataHub data, ordered by max_demand_value descending
select site_id, round(max(standardized_measured_max_demand_usage_value), 2) as max_demand_value, any_value(standardized_measured_max_demand_usage_unit_of_measure) as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where standardized_measured_max_demand_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by max_demand_value desc;

You also may want to look at max_demand_values for a single service type:

-- by site_id for the 2023 calendar year where the service_type is electric, ordered by max_demand_value descending
select site_id, round(max(standardized_measured_max_demand_usage_value), 2) as max_demand_value, any_value(standardized_measured_max_demand_usage_unit_of_measure) as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where year(period_end_date) = '2023'
    and year(period_start_date) = '2023'
    and standardized_measured_max_demand_usage_value is not null
    and site_id is not null
    and service_type = 'electric'
  group by site_id, service_type
  order by max_demand_value desc;

If you want to include statements that straddle 2022 and 2023 or 2023 and 2024 you can update the query like this:

-- by site_id for the 2023 calendar year including statements that straddle adjacent years
select site_id, round(max(standardized_measured_max_demand_usage_value), 2) as max_demand_value, any_value(standardized_measured_max_demand_usage_unit_of_measure) as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where (year(period_end_date) = '2023' or year(period_start_date) = '2023')
    and standardized_measured_max_demand_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by site_id;

You may also want to group by unit of measure if your statement data contains usages that cannot be standardized currently:

-- by site_id for the 2023 calendar year, original unit of measure
select site_id, round(max(original_measured_max_demand_usage_value), 2) as max_demand_value, original_measured_max_demand_usage_unit_of_measure as unit_of_measure, service_type
  from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
  where year(period_end_date) = '2023'
    and year(period_start_date) = '2023'
    and original_measured_max_demand_usage_value is not null
    and site_id is not null
  group by site_id, service_type, unit_of_measure
  order by site_id;

Return format

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

SITE_ID	MAX_DEMAND_VALUE	MAX_DEMAND_VALUE_UNIT_OF_MEASURE	SERVICE_TYPE
1ECE16…	18602.00	kW	electric
1ECE89…	111.00	ccf	natural gas