支払い失敗時の回復を測定する

WITH failures AS (
  SELECT
    invoice_id,
    MIN(timestamp_utc) AS first_failed_at,
    MAX(amount_usd) AS amount_usd,
    MIN(failure_type) AS failure_type
  FROM invoice_events
  WHERE event_name = 'invoice_payment_failed'
    AND timestamp_utc >= now() - INTERVAL '60 days'
  GROUP BY invoice_id
),
recoveries AS (
  SELECT
    invoice_id,
    MIN(timestamp_utc) AS recovered_at
  FROM invoice_events
  WHERE event_name = 'invoice_paid'
  GROUP BY invoice_id
)
SELECT
  failures.failure_type,
  COUNT(*) AS failed_invoices,
  SUM(CASE
    WHEN recoveries.recovered_at > failures.first_failed_at THEN 1
    ELSE 0
  END) AS recovered_invoices,
  100.0 * SUM(CASE
    WHEN recoveries.recovered_at > failures.first_failed_at THEN 1
    ELSE 0
  END) / NULLIF(COUNT(*), 0) AS recovery_rate_pct,
  SUM(CASE
    WHEN recoveries.recovered_at IS NULL THEN failures.amount_usd
    ELSE 0.0
  END) AS unrecovered_amount_usd
FROM failures
LEFT JOIN recoveries
  ON recoveries.invoice_id = failures.invoice_id
GROUP BY failures.failure_type
ORDER BY unrecovered_amount_usd DESC;