Skip to main content
This page provides practical SQL query examples for working with your Metronome data exports. Use these queries as starting points for your own analysis and reporting.
All examples use standard SQL syntax. Adjust date functions and syntax as needed for your specific data warehouse (Snowflake, BigQuery, Redshift, etc.).

​
Core Entities

​
Customers

Count all non-archived customers.
SELECT COUNT(0)
FROM customer
WHERE archived_at IS NULL;
Tables: customer

​
Events

Track event ingestion volume over time.
SELECT DATE_TRUNC('MONTH', timestamp) AS month,
       COUNT(0) AS event_count
FROM events
GROUP BY 1
ORDER BY 1;
Tables: events

​
Invoicing

​
Finalized Invoices

Calculate total invoice counts and amounts by month.
SELECT DATE_TRUNC("MONTH", end_timestamp) AS month,
       COUNT(0) AS invoice_count,
       SUM(total) AS invoice_total
FROM invoice
GROUP BY 1
ORDER BY 1;
Tables: invoice
Break down monthly revenue by line item type.
SELECT DATE_TRUNC("MONTH", i.end_timestamp) AS month,
       li.line_item_type,
       SUM(li.total) AS total
FROM invoice i
JOIN line_item li ON i.id = li.invoice_id
GROUP BY 1, 2
ORDER BY 2, 1;
Tables: invoice, line_item

​
Draft Invoices

Track draft invoice progression over time.
SELECT snapshot_time,
       COUNT(0) AS invoice_count,
       SUM(total) AS invoice_total
FROM draft_invoice
GROUP BY 1
ORDER BY 1;
Tables: draft_invoice
Get the latest draft invoice totals grouped by contract.
SELECT contract_id,
       COUNT(0) AS invoice_count,
       SUM(total) AS invoice_total
FROM draft_invoice
WHERE snapshot_time = (SELECT MAX(snapshot_time) FROM draft_invoice)
GROUP BY 1
ORDER BY 1;
Tables: draft_invoice

​
Invoice Breakdowns

Analyze line item metrics from the most recent draft breakdowns snapshot.
WITH max_draft_breakdown_snapshot AS (
    SELECT max(snapshot_timestamp) AS max_snapshot_ts
    FROM breakdowns_draft_invoices
)
SELECT i.breakdown_start_timestamp,
       li.name,
       SUM(li.quantity) AS quantity,
       SUM(li.total/100) AS total_dollars
FROM breakdowns_draft_invoices i
JOIN breakdowns_draft_line_items li
  ON i.id = li.invoice_breakdown_id
  AND i.snapshot_timestamp = li.snapshot_timstamp
WHERE i.environment_type = 'PRODUCTION'
  AND i.snapshot_timestamp = (SELECT max_snapshot_ts FROM max_draft_breakdown_snapshot)
  AND li.total >= 0
GROUP BY 1, 2
ORDER BY 1 DESC;
Tables: breakdowns_draft_invoices, breakdowns_draft_line_items
Get detailed draft line item breakdowns per customer and invoice.
WITH max_draft_breakdown_snapshot AS (
    SELECT max(snapshot_timestamp) AS max_snapshot_ts
    FROM breakdowns_draft_invoices
)
SELECT i.customer_id,
       i.breakdown_start_timestamp,
       i.invoice_id,
       li.name,
       li.quantity,
       li.total/100 AS total_dollars
FROM breakdowns_draft_invoices i
JOIN breakdowns_draft_line_items li
  ON i.id = li.invoice_breakdown_id
  AND i.snapshot_timestamp = li.snapshot_timstamp
WHERE i.environment_type = 'PRODUCTION'
  AND i.snapshot_timestamp = (SELECT max_snapshot_ts FROM max_draft_breakdown_snapshot)
GROUP BY 1, 2
ORDER BY 1 DESC;
Tables: breakdowns_draft_invoices, breakdowns_draft_line_items

​
Contracts

List all archived contracts.
SELECT *
FROM contracts_contracts
WHERE archived_at IS NOT NULL;
Tables: contracts_contracts
Get the latest pricing overrides for a specific contract.
SELECT *
FROM contracts_overrides
WHERE contract_id = '<contract_id>'
ORDER BY updated_at DESC;
Tables: contracts_overrides
Replace <contract_id> with your actual contract ID.
Analyze rate card coverage by counting active entries.
SELECT crc.id AS rate_card_id, COUNT(*)
FROM contracts_rate_cards crc
JOIN contracts_rate_card_entries crce ON crc.id = crce.rate_card_id
WHERE crce.ending_before > NOW()
GROUP BY 1
ORDER BY 1;
Tables: contracts_rate_cards, contracts_rate_card_entries

​
Alerts

Get all active alerts configured to send webhooks.
SELECT id, name, alert_type, threshold
FROM alert
WHERE webhooks_enabled = TRUE
  AND disabled_at IS NULL;
Tables: alert
Analyze alert trigger frequency by day.
SELECT DATE_TRUNC("DAY", ah.created_at) AS alert_date,
       alert.name,
       COUNT(alert.id) AS triggered_count
FROM customer_alert_history ah
JOIN alert ON ah.alert_id = alert.id
GROUP BY 1, 2
ORDER BY 1;
Tables: customer_alert_history, alert
⌘I