追蹤事件架構版本採用情況

WITH version_counts AS (
  SELECT
    event_name,
    producer_version,
    schema_version,
    COUNT(*) AS events
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  GROUP BY event_name, producer_version, schema_version
),
producer_totals AS (
  SELECT
    event_name,
    producer_version,
    SUM(events) AS producer_events
  FROM version_counts
  GROUP BY event_name, producer_version
)
SELECT
  counts.event_name,
  counts.producer_version,
  counts.schema_version,
  counts.events,
  100.0 * counts.events / NULLIF(totals.producer_events, 0)
    AS version_share_pct
FROM version_counts AS counts
JOIN producer_totals AS totals
  ON totals.event_name = counts.event_name
  AND totals.producer_version = counts.producer_version
ORDER BY counts.event_name, counts.producer_version, counts.events DESC;