배달 못한 편지 대기열 증가 측정

SELECT
  date_trunc('day', timestamp_utc) AS day,
  queue_name,
  SUM(CASE
    WHEN event_name = 'dead_letter_created' THEN 1 ELSE 0
  END) AS created_jobs,
  SUM(CASE
    WHEN event_name = 'dead_letter_resolved' THEN 1 ELSE 0
  END) AS resolved_jobs,
  SUM(CASE
    WHEN event_name = 'dead_letter_created' THEN 1
    WHEN event_name = 'dead_letter_resolved' THEN -1
    ELSE 0
  END) AS net_queue_change
FROM job_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
  AND event_name IN ('dead_letter_created', 'dead_letter_resolved')
GROUP BY date_trunc('day', timestamp_utc), queue_name
ORDER BY day, queue_name;