SQL cookbook

SELECT COUNT(0)
FROM customer
WHERE archived_at IS NULL;

SELECT DATE_TRUNC('MONTH', timestamp) AS month,
       COUNT(0) AS event_count
FROM events
GROUP BY 1
ORDER BY 1;

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;

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;

SELECT snapshot_time,
       COUNT(0) AS invoice_count,
       SUM(total) AS invoice_total
FROM draft_invoice
GROUP BY 1
ORDER BY 1;

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;

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;

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;

SELECT *
FROM contracts_contracts
WHERE archived_at IS NOT NULL;

SELECT *
FROM contracts_overrides
WHERE contract_id = '<contract_id>'
ORDER BY updated_at DESC;

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;

SELECT id, name, alert_type, threshold
FROM alert
WHERE webhooks_enabled = TRUE
  AND disabled_at IS 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;