General Explanation

What is this recipe?

This is a recipe to find what tariff is tied to your greatest expense in the 2023 calendar year.

Use case

What is this recipe for?

This recipe is for users who want to find out what tariff is associated with the highest expense that their organization incurred during the 2023 calendar year. Knowing the tariff associated with your highest expense can allow you insight into areas for tariff optimization.

Snowflake SQL

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

-- top charge by site for the 2023 calendar year
select charge_id, round(charge_amount, 2) as charge_amount_rounded, charge_amount_currency, rate_or_tariff_name_as_printed, charge_usage_units_used, charge_usage_unit_of_measure, service_type
from (
	select cc.*,
		row_number() over (
		  partition by site_id order by charge_amount desc
		) as seqnum
	from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_charges_current cc where year(period_end_date) = '2023' and year(period_start_date) = '2023'
)
where seqnum = 1 order by charge_amount desc;

This expression returns the highest charge amount per site for the 2023 calendar year. In this expression it returns the charge_id, the rounded charge_amount, the tariff name as printed on the statement that is associated with the charge, the number of usage units used if the charge is associated with a usage, the charge usage’s units of measure if applicable, and the service_type associated with the charge.

This expression uses the partition keyword within snowflake to get the maximum charge by site. If you want to get the top five charges by site you can update the query like so to get the top five from each partition instead of just the top one:

-- top five charges by site for the 2023 calendar year
select charge_id, site_id, round(charge_amount, 2) as charge_amount_rounded, charge_amount_currency, rate_or_tariff_name_as_printed, charge_usage_units_used, charge_usage_unit_of_measure, service_type
from (
	select cc.*,
		row_number() over (
		  partition by site_id order by charge_amount desc
		) as seqnum
	from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_charges_current cc where year(period_end_date) = '2023' and year(period_start_date) = '2023'
)
where seqnum <= 5 order by site_id, charge_amount desc;

Return format

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