> ## 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.

# Cookbook SQL

> Requêtes SQL courantes et exemples pour analyser vos exports d'entrepôt de données Metronome

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.

<Tip>
  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.).
</Tip>

## Entités principales

### Clients

<AccordionGroup>
  <Accordion title="Récupérer le total des clients actifs" icon="users">
    Comptez tous les clients non archivés.

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

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

### Événements

<AccordionGroup>
  <Accordion title="Récupérer le nombre d'événements par mois" icon="chart-bar">
    Suivez le volume d'ingestion d'événements au fil du temps.

    ```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`](/fr/guides/reporting-insights/data-export/database-reference#events)
  </Accordion>
</AccordionGroup>

## Facturation

### Factures finalisées

<AccordionGroup>
  <Accordion title="Récupérer les totaux de factures mensuels" icon="file-invoice-dollar">
    Calculez le nombre total de factures et les montants par mois.

    ```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`](/fr/guides/reporting-insights/data-export/database-reference#invoice)
  </Accordion>

  <Accordion title="Récupérer les totaux mensuels par type de ligne" icon="list">
    Décomposez les revenus mensuels par type de ligne.

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

### Factures brouillon

<AccordionGroup>
  <Accordion title="Récupérer les totaux quotidiens des factures brouillon" icon="file-invoice">
    Suivez la progression des factures brouillon au fil du temps.

    ```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`](/fr/guides/reporting-insights/data-export/database-reference#draft-invoice)
  </Accordion>

  <Accordion title="Récupérer le total des factures brouillon par contrat depuis le dernier snapshot" icon="file-contract">
    Obtenez les derniers totaux de factures brouillon groupés par contrat.

    ```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`](/fr/guides/reporting-insights/data-export/database-reference#draft-invoice)
  </Accordion>
</AccordionGroup>

### Décompositions de factures

<AccordionGroup>
  <Accordion title="Récupérer la quantité agrégée et le total quotidiens des lignes brouillon" icon="table">
    Analysez les métriques de lignes à partir du dernier snapshot de décompositions brouillon.

    ```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`](/fr/guides/reporting-insights/data-export/database-reference#breakdowns-draft-invoices), [`breakdowns_draft_line_items`](/fr/guides/reporting-insights/data-export/database-reference#breakdowns-draft-line-items)
  </Accordion>

  <Accordion title="Récupérer les totaux des lignes de factures brouillon quotidiennes par client" icon="user-tag">
    Obtenez des décompositions détaillées de lignes brouillon par client et par facture.

    ```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`](/fr/guides/reporting-insights/data-export/database-reference#breakdowns-draft-invoices), [`breakdowns_draft_line_items`](/fr/guides/reporting-insights/data-export/database-reference#breakdowns-draft-line-items)
  </Accordion>
</AccordionGroup>

## Contrats

<AccordionGroup>
  <Accordion title="Récupérer les contrats archivés" icon="file-contract">
    Listez tous les contrats archivés.

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

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

  <Accordion title="Récupérer les overrides les plus récents sur un contrat" icon="pen-to-square">
    Obtenez les derniers overrides de tarification pour un contrat spécifique.

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

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

    <Tip>
      Remplacez `<contract_id>` par votre ID de contrat réel.
    </Tip>
  </Accordion>

  <Accordion title="Récupérer le nombre d'entrées de rate card actives par rate card" icon="money-bill">
    Analysez la couverture des rate cards en comptant les entrées actives.

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

## Alertes

<AccordionGroup>
  <Accordion title="Récupérer les alertes avec webhooks activés" icon="bell">
    Obtenez toutes les alertes actives configurées pour envoyer des webhooks.

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

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

  <Accordion title="Récupérer le nombre d'alertes par jour au cours de la dernière semaine" icon="chart-line">
    Analysez la fréquence de déclenchement des alertes par jour.

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