Set up SQL billable metrics

You can write SQL to define your billable metric in Metronome. On the billable metric creation page, use the code editor to test and validate your query before saving.

Supported Functions

Aggregations

• COUNT: Counts the number of rows.
• SUM: Sums numbers.
• MAX: Takes the maximum of numbers.
• MIN: Takes the minimum of numbers.
• AVG: Averages numbers.
• EARLIEST: Returns the earliest value of a column based on its timestamp.
• LATEST: Returns the latest value of a column based on its timestamp.
• COUNT DISTINCT: Counts the distinct values in the expression.

Math

• +, -, *, /
• =, !=, >, <, ≥, ≤
• LEAST: Returns the least of its arguments.
• GREATEST: Returns the maximum of its arguments.
• ROUND: Rounds a number to a specified number of decimal places.
• CEIL: Returns the smallest integer value that is greater or equal to the input.
• FLOOR: Returns the largest integer value that is less than or equal to the input.

Logic

• AND
• OR
• NOT
• CASE WHEN
• IS NULL
• IS NOT NULL
• IN
• NOT IN
• =
• !=

Dates

• DATE_TRUNC: Truncates the timestamp field to the hour or day.

Casting

• CAST

Writing a SQL query

• Query the events table.
• You can access the event_type field in the SQL editor by using event_type.
• You can access the timestamp field in the SQL editor by using timestamp.
• You can access any fields in the properties dictionary by using properties.field_name
• All fields in the properties dictionary are read into Metronome as strings. Cast them to the desired type. For example, CAST(properties.field_name as INT) will cast the field_name field as an integer.
• When returning multiple columns from the SQL query, the first column returned should contain the quantity to be charged. On the Contract product, you can use the other columns as presentation group keys to break out their costs on the invoice.

Examples

A sum of the max of value per cluster_id

select sum(max_value) from (
	select
	max(cast(properties.value as double)) as max_value,
	properties.cluster_id as cluster_id
	from events
	group by properties.cluster_id
)

An average of the hourly sum of (proration_factor *quantity)

select avg(value) from (
	select
	cast(properties.proration_factor as double) * cast(properties.quantity as int) as value,
	date_trunc('hour', timestamp) as hour
	from events
	group by date_trunc('hour', timestamp)
)

Imagine that for each organization_id, we want to bill on a count of unique connection_ids. On the final invoice, we want to show the count of unique connection_ids for each group. To encode this, we’ll return one value per organization_id in the SQL query. Then, on the Contract Product, we specify the organization_id as a presentation_group_key.

select
  count(distinct properties.connection_id) as unique_connection_ids, 
  properties.organization_id as organization_id
  from events
  group by properties.organization_id