按帳戶計算使用配額消耗

WITH deduplicated_usage AS (
  SELECT
    meter_event_id,
    account_id,
    plan,
    MAX(billable_units) AS billable_units,
    MAX(included_units) AS included_units
  FROM usage_meter_events
  WHERE timestamp_utc >= date_trunc('month', now())
  GROUP BY meter_event_id, account_id, plan
)
SELECT
  account_id,
  plan,
  COUNT(*) AS meter_events,
  SUM(billable_units) AS used_units,
  MAX(included_units) AS included_units,
  100.0 * SUM(billable_units)
    / NULLIF(MAX(included_units), 0) AS quota_used_pct,
  SUM(billable_units) - MAX(included_units) AS overage_units
FROM deduplicated_usage
GROUP BY account_id, plan
ORDER BY quota_used_pct DESC;