General Explanation

What is this recipe?

This recipe shows how to aggregate monthly, quarterly, or annual meter usage data by site ID and unit of measure (UOM). This recipe is different from Group & Sort Aggregated Meter Usage Data by Site ID & Service Type in that it includes aggregates usages that could not be standardized:

horsepower
residential cooling
hecta liters
nm3/h
sm3
m3/h
kgh
kg
unit
undefined
gallons (for propane service type)

Use case

What is this recipe for?

This recipe is for users that want to see all aggregated usage values for all meters for a given site and UOM, whether or not the units could be standardized. This can be useful for analyzing usages at different facilities when the meter-level view is too granular.

Snowflake SQL

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

This first query shows how to aggregate the monthly usage values by site_id, unit_of_measure, and calendarized_month. This sums the monthly meter usage values so that you can see the total usage by site and UOM. It unions the standardized with the non-standardized usages to include the usages that could not be standardized.

Note that this query also selects the service_type. This is because some UOM and service_type pairs cannot be standardized because they are either not supported or are invalid (like gallons of propane).

with standardized as (
    select site_id
        , calendarized_month
        , round(sum(standardized_monthly_measured_usage), 2) as monthly_measured_usage
        , standardized_monthly_measured_usage_unit_of_measure as unit_of_measure
        , any_value(service_type) as service_type
    from <YOUR_SNOWFLAKE_SHARE>.shares.monthly_usages
    where 
        standardized_monthly_measured_usage is not null
        and site_id is not null
    group by site_id, calendarized_month, unit_of_measure
)

, non_standardized as (
    select site_id
        , calendarized_month
        , round(sum(original_monthly_measured_usage), 2) as monthly_measured_usage
        , original_monthly_measured_usage_unit_of_measure as unit_of_measure
        , any_value(service_type) as service_type
    from <YOUR_SNOWFLAKE_SHARE>.shares.monthly_usages
    where 
        standardized_monthly_measured_usage is null
        and original_monthly_measured_usage is not null
        and site_id is not null
    group by site_id, calendarized_month, unit_of_measure
)

select * from standardized
union all
select * from non_standardized
order by site_id, calendarized_month, unit_of_measure;

This query orders by site, month, and UOM. The order by is applied to the result of the union all so that both standardized and non-standardized usages are sorted together.

You can change the order by statement to surface other types of information:

To sort by the highest usage by site by UOM regardless of month:

order by site_id, unit_of_measure, monthly_measured_usage desc

To sort by just the highest usage by UOM:

order by unit_of_measure, monthly_measured_usage desc

The next query shows how to aggregate quarterly usages.

with standardized as (
    select site_id
        , calendarized_quarter
        , round(sum(standardized_quarterly_measured_usage), 2) as quarterly_measured_usage
        , standardized_quarterly_measured_usage_unit_of_measure as unit_of_measure
        , any_value(service_type) as service_type
    from <YOUR_SNOWFLAKE_SHARE>.shares.quarterly_usages
    where 
        standardized_quarterly_measured_usage is not null
        and site_id is not null
    group by site_id, calendarized_quarter, unit_of_measure
)

, non_standardized as (
    select site_id
        , calendarized_quarter
        , round(sum(original_quarterly_measured_usage), 2) as quarterly_measured_usage
        , original_quarterly_measured_usage_unit_of_measure as unit_of_measure
        , any_value(service_type) as service_type
    from <YOUR_SNOWFLAKE_SHARE>.shares.quarterly_usages
    where 
        standardized_quarterly_measured_usage is null
        and original_quarterly_measured_usage is not null
        and site_id is not null
    group by site_id, calendarized_quarter, unit_of_measure
)

select * from standardized
union all
select * from non_standardized
order by site_id, calendarized_quarter, unit_of_measure;

The additional sort by expressions documented with the first query can also be used here.

This last query shows how to aggregate annual usages.

with standardized as (
    select site_id
        , calendarized_annual
        , round(sum(standardized_annual_measured_usage), 2) as annual_measured_usage
        , standardized_annual_measured_usage_unit_of_measure as unit_of_measure
        , any_value(service_type) as service_type
    from <YOUR_SNOWFLAKE_SHARE>.shares.annual_usages
    where 
        standardized_annual_measured_usage is not null
        and site_id is not null
    group by site_id, calendarized_annual, unit_of_measure
)

, non_standardized as (
    select site_id
        , calendarized_annual
        , round(sum(original_annual_measured_usage), 2) as annual_measured_usage
        , original_annual_measured_usage_unit_of_measure as unit_of_measure
        , any_value(service_type) as service_type
    from <YOUR_SNOWFLAKE_SHARE>.shares.annual_usages
    where 
        standardized_annual_measured_usage is null
        and original_annual_measured_usage is not null
        and site_id is not null
    group by site_id, calendarized_annual, unit_of_measure
)

select * from standardized
union all
select * from non_standardized
order by site_id, calendarized_annual, unit_of_measure;

The additional sort by expressions documented with the first query can also be used here.

Return format

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

Monthly usage by site and service type

SITE_ID	CALENDARIZED_MONTH	MONTHLY_MEASURED_USAGE	UNIT_OF_MEASURE
sit_1EC…	2023-01	2342.32	gallons
sit_38D…	2023-01	2838.00	kwh
sit_38D…	2023-01	76.00	unit
sit_38D…	2023-01	199.02	sm3

Quarterly usage by site and service type

SITE_ID	CALENDARIZED_QUARTER	QUARTERLY_MEASURED_USAGE	UNIT_OF_MEASURE
sit_1EC…	2023-q1	3828.00	gallons
sit_38D…	2023-q1	5521.10	kwh
sit_38D…	2023-q1	238.93	unit
sit_38D…	2023-q1	430.0	sm3

Annual usage by site and service type

SITE_ID	CALENDARIZED_ANNUAL	ANNUAL_MEASURED_USAGE	UNIT_OF_MEASURE
sit_1EC…	2023	234283.00	gallons
sit_38D…	2023	283802.23	kwh
sit_38D…	2023	1293.38	unit
sit_38D…	2023	801.37	sm3