General Explanation

What is this recipe?

This recipe is to group all of your total consumption usages by site and service type for a given year. Most of the example queries given here make sure that the start and end date of each statement is within the desired year, but you can include statements whose measurement periods partially overlap with the year as well.

Use case

What is this recipe for?

This recipe is for users who would like to see how much total utility usage (energy, gas, water, etc…) they’re using by site for a given year.

Snowflake SQL

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

select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_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_total_consumption_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by site_id;

The above query calculates your total consumption for a given site by service type for the 2023 calendar year.

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

select meter_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_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_total_consumption_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 total consumption to identify your sites that have the most usage:

select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_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_total_consumption_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by total_consumption desc;

You also may want to look at total yearly consumption for a single service type:

select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_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 service_type = 'electric'
    and standardized_measured_total_consumption_usage_value is not null
    and site_id is not null
  group by site_id, service_type
  order by total_consumption desc;

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

select site_id, round(sum(standardized_measured_total_consumption_usage_value), 2) as total_consumption, any_value(standardized_measured_total_consumption_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_total_consumption_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:

select site_id, round(sum(original_measured_total_consumption_usage_value), 2) as total_consumption, original_measured_total_consumption_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_total_consumption_usage_value is not null
    and site_id is not null
  group by site_id, unit_of_measure, service_type
  order by site_id;

Return format

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

SITE_ID	TOTAL_CONSUMPTION	UNIT_OF_MEASURE	SERVICE_TYPE
1ECE16…	412440.00	kWh	electric
1ECE89…	502.00	cubic_meters	natural gas
1EDBF4…	23188.00	gallons	water