Passer au contenu principal
Cette page fournit des exemples pratiques de requêtes SQL pour travailler avec vos exports de données Metronome. Utilisez ces requêtes comme points de départ pour vos propres analyses et rapports.
Tous les exemples utilisent une syntaxe SQL standard. Adaptez les fonctions de date et la syntaxe selon votre entrepôt de données spécifique (Snowflake, BigQuery, Redshift, etc.).

​
Entités principales

​
Clients

Comptez tous les clients non archivés.
SELECT COUNT(0)
FROM customer
WHERE archived_at IS NULL;
Tables : customer

​
Événements

Suivez le volume d’ingestion d’événements au fil du temps.
SELECT DATE_TRUNC('MONTH', timestamp) AS month,
       COUNT(0) AS event_count
FROM events
GROUP BY 1
ORDER BY 1;
Tables : events

​
Facturation

​
Factures finalisées

Calculez le nombre total de factures et les montants par mois.
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
Décomposez les revenus mensuels par type de ligne.
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

​
Factures brouillon

Suivez la progression des factures brouillon au fil du temps.
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
Obtenez les derniers totaux de factures brouillon groupés par contrat.
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

​
Décompositions de factures

Analysez les métriques de lignes à partir du dernier snapshot de décompositions brouillon.
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, breakdowns_draft_line_items
Obtenez des décompositions détaillées de lignes brouillon par client et par facture.
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, breakdowns_draft_line_items

​
Contrats

Listez tous les contrats archivés.
SELECT *
FROM contracts_contracts
WHERE archived_at IS NOT NULL;
Tables : contracts_contracts
Obtenez les derniers overrides de tarification pour un contrat spécifique.
SELECT *
FROM contracts_overrides
WHERE contract_id = '<contract_id>'
ORDER BY updated_at DESC;
Tables : contracts_overrides
Remplacez <contract_id> par votre ID de contrat réel.
Analysez la couverture des rate cards en comptant les entrées actives.
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

​
Alertes

Obtenez toutes les alertes actives configurées pour envoyer des webhooks.
SELECT id, name, alert_type, threshold
FROM alert
WHERE webhooks_enabled = TRUE
  AND disabled_at IS NULL;
Tables : alert
Analysez la fréquence de déclenchement des alertes par jour.
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