Billable metrics defined using the SQL Editor are formatted as SQL queries that are run against a table called events, which includes all of the raw usage events that are sent in to Metronome. Similar to the Basic Filters editor, billable metric SQL queries should include a set of filters that identify the correct usage events to query over, and an aggregation to turn these events into a value that Metronome will use as the quantity for downstream line items. Metronome handles filtering down the SQL queries for individual customers over the course of their billing period. You do not need to include this logic in your queries - simply focus on filtering and aggregation!

​
Supported features and functionality​

Here is how you can access elements of your usage event within the 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
If your query returns more than one column, Metronome first looks for a column called value to use as the column for the metric quantity. If no column value exists, Metronome uses the first column returned in the query. Any other columns returned can be used as group keys in downstream pricing and packaging.
INFOIf extra columns are returned but are not used as presentation or pricing group keys, Metronome sums over all of the results to generate a single quantity for the metric.

​
Supported functions and operators​

The SQL Editor supports these functions and operators.
  • 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

​
Example creation flow​

As an example of a more complex scenario, use the SQL Editor to create billable metric that tracks the daily average of storage used over a billing period.
  1. Navigate to the Billable Metrics section in the Metronome app.
  2. Click + Add new Billable Metric.
  3. Choose SQL query.
  4. Name your metric (for example, Storage latest daily max).
  5. Enter your SQL query: 
    SELECT SUM(max_daily_storage) / SUM(num_days) as value, user_id, region
FROM (
    SELECT
        date_trunc('day', timestamp) as date,
        properties.user_id as user_id,
        properties.region as region,
        MAX(properties.storage_used) as max_daily_storage,
        1 as num_days
    FROM events
    WHERE event_type = 'storage_heartbeat'
    GROUP BY date, user_id, region
)
GROUP BY user_id, region
INFOIn this example, user_id is returned so that it can be defined as a presentation_group_key when creating a product from this metric. This allows you to display invoices broken out by user_id. region is returned so that it can be defined as a pricing_group_key. This would allow you to add different rates for this billable metric for values of us-east-1, us-west-1, or ap-south-1.
  1. Preview your metric against any existing usage data to ensure the results are correct. When complete, save your metric.

​
Limitations​

When using SQL Billable Metrics, all costs are incurred at the last instant of the billing period. For example, imagine you have a simple SUM metric but you build it using a SQL Billable Metric. On day 1 of the customer’s billing period, you send in a value of 5. On day 2, you send in a value of 10. On day 3, you send in a value of 15. Let’s imagine the customer’s billing period spans from 1/1/2025 to 2/1/2025. The full quantity of 30 uses will be incurred at the last time window.
  • The final price for the billing period will be used for the full quantity. For example, if you schedule that the rate is $10 from 1/1/2025 to 1/15/2025, and the rate is $20 from 1/15/2025 onward - the full quantity of 30 will be priced at $20.
  • Credits and commits must cover the last instant of the billing period to apply against the spend.
  • When using the invoice-breakdowns endpoint, the costs will be incurred in the last time window of the billing period.