> ## Documentation Index
> Fetch the complete documentation index at: https://docs.metronome.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL cookbook

> Common SQL queries and examples for analyzing your Metronome data warehouse exports

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.

<Tip>
  All examples use standard SQL syntax. Adjust date functions and syntax as
  needed for your specific data warehouse (Snowflake, BigQuery, Redshift, etc.).
</Tip>

## Core Entities

### Customers

<AccordionGroup>
  <Accordion title="Fetch total active customers" icon="users">
    Count all non-archived customers.

    ```sql theme={null}
    SELECT COUNT(0)
    FROM customer
    WHERE archived_at IS NULL;
    ```

    **Tables:** [`customer`](/guides/reporting-insights/data-export/database-reference#customer)
  </Accordion>
</AccordionGroup>

### Events

<AccordionGroup>
  <Accordion title="Fetch event count per month" icon="chart-bar">
    Track event ingestion volume over time.

    ```sql theme={null}
    SELECT DATE_TRUNC('MONTH', timestamp) AS month,
           COUNT(0) AS event_count
    FROM events
    GROUP BY 1
    ORDER BY 1;
    ```

    **Tables:** [`events`](/guides/reporting-insights/data-export/database-reference#events)
  </Accordion>
</AccordionGroup>

## Invoicing

### Finalized Invoices

<AccordionGroup>
  <Accordion title="Fetch monthly invoice totals" icon="file-invoice-dollar">
    Calculate total invoice counts and amounts by month.

    ```sql theme={null}
    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`](/guides/reporting-insights/data-export/database-reference#invoice)
  </Accordion>

  <Accordion title="Fetch monthly totals by line item type" icon="list">
    Break down monthly revenue by line item type.

    ```sql theme={null}
    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`](/guides/reporting-insights/data-export/database-reference#invoice), [`line_item`](/guides/reporting-insights/data-export/database-reference#line-item)
  </Accordion>
</AccordionGroup>

### Draft Invoices

<AccordionGroup>
  <Accordion title="Fetch daily draft invoice totals" icon="file-invoice">
    Track draft invoice progression over time.

    ```sql theme={null}
    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`](/guides/reporting-insights/data-export/database-reference#draft-invoice)
  </Accordion>

  <Accordion title="Fetch draft invoice total by contract from most recent snapshot" icon="file-contract">
    Get the latest draft invoice totals grouped by contract.

    ```sql theme={null}
    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`](/guides/reporting-insights/data-export/database-reference#draft-invoice)
  </Accordion>
</AccordionGroup>

### Invoice Breakdowns

<AccordionGroup>
  <Accordion title="Fetch daily draft aggregate line item quantity and total" icon="table">
    Analyze line item metrics from the most recent draft breakdowns snapshot.

    ```sql theme={null}
    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_timestamp
    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`](/guides/reporting-insights/data-export/database-reference#breakdowns-draft-invoices), [`breakdowns_draft_line_items`](/guides/reporting-insights/data-export/database-reference#breakdowns-draft-line-items)
  </Accordion>

  <Accordion title="Fetch daily draft invoice line item totals by customer" icon="user-tag">
    Get detailed draft line item breakdowns per customer and invoice.

    ```sql theme={null}
    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_timestamp
    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`](/guides/reporting-insights/data-export/database-reference#breakdowns-draft-invoices), [`breakdowns_draft_line_items`](/guides/reporting-insights/data-export/database-reference#breakdowns-draft-line-items)
  </Accordion>
</AccordionGroup>

## Contracts

<AccordionGroup>
  <Accordion title="Fetch archived contracts" icon="file-contract">
    List all archived contracts.

    ```sql theme={null}
    SELECT *
    FROM contracts_contracts
    WHERE archived_at IS NOT NULL;
    ```

    **Tables:** [`contracts_contracts`](/guides/reporting-insights/data-export/database-reference#contracts-contracts)
  </Accordion>

  <Accordion title="Fetch the most recent overrides on a contract" icon="pen-to-square">
    Get the latest pricing overrides for a specific contract.

    ```sql theme={null}
    SELECT *
    FROM contracts_overrides
    WHERE contract_id = '<contract_id>'
    ORDER BY updated_at DESC;
    ```

    **Tables:** [`contracts_overrides`](/guides/reporting-insights/data-export/database-reference#contracts-overrides)

    <Tip>
      Replace `<contract_id>` with your actual contract ID.
    </Tip>
  </Accordion>

  <Accordion title="Fetch active rate card entry count per rate card" icon="money-bill">
    Analyze rate card coverage by counting active entries.

    ```sql theme={null}
    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`](/guides/reporting-insights/data-export/database-reference#contracts-rate-cards), [`contracts_rate_card_entries`](/guides/reporting-insights/data-export/database-reference#contracts-rate-card-entries)
  </Accordion>
</AccordionGroup>

## Alerts

<AccordionGroup>
  <Accordion title="Fetch alerts with webhooks enabled" icon="bell">
    Get all active alerts configured to send webhooks.

    ```sql theme={null}
    SELECT id, name, alert_type, threshold
    FROM alert
    WHERE webhooks_enabled = TRUE
      AND disabled_at IS NULL;
    ```

    **Tables:** [`alert`](/guides/reporting-insights/data-export/database-reference#alert)
  </Accordion>

  <Accordion title="Fetch count of alerts by day over the last week" icon="chart-line">
    Analyze alert trigger frequency by day.

    ```sql theme={null}
    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`](/guides/reporting-insights/data-export/database-reference#customer-alert-history), [`alert`](/guides/reporting-insights/data-export/database-reference#alert)
  </Accordion>
</AccordionGroup>