General Explanation

What is this recipe?

This recipe shows how to aggregate monthly, quarterly, or annual meter usage data by site ID, allowing for insights into usage patterns across different sites over time.

Use case

What is this recipe for?

This recipe is for users that want to see aggregated usage values for all meters for a given site and service type. This can be useful for analyzing usages at different facilities when the meter-level view is too granular. Grouping by location can also help in calculating emissions if emission coefficients can be correlated to these locations.

Snowflake SQL

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

1. This first query shows how to aggregate the monthly usage values by site_id, service_type, and calendarized_month. This sums the monthly meter usage values so that you can see the total usage by site and service type.

select site_id
    , calendarized_month
    , round(sum(standardized_monthly_measured_usage), 2) as monthly_measured_usage
    , any_value(standardized_monthly_measured_usage_unit_of_measure) as unit_of_measure
    , 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, service_type
order by site_id, calendarized_month, service_type;

This query orders by site, month, and service type. You can change the order by statement to surface other types of information:

• To sort by the highest usage by site by service type regardless of month:

order by site_id, service_type, monthly_measured_usage desc

• To sort by just the highest usage by service type:

order by service_type, monthly_measured_usage desc

1. The next query shows how to aggregate quarterly usages.

select site_id
    , calendarized_quarter
    , round(sum(standardized_quarterly_measured_usage), 2) as quarterly_measured_usage
    , any_value(standardized_quarterly_measured_usage_unit_of_measure) as unit_of_measure
    , 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, service_type
order by site_id, calendarized_quarter, service_type;

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

1. This last query shows how to aggregate annual usages.

select site_id
    , calendarized_annual
    , round(sum(standardized_annual_measured_usage), 2) as annual_measured_usage
    , any_value(standardized_annual_measured_usage_unit_of_measure) as unit_of_measure
    , 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, service_type
order by site_id, calendarized_annual, service_type;

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

Quarterly usage by site and service type

Annual usage by site and service type