Measure LLM Cache Savings and Retry Cost

SELECT
  model,
  COUNT(*) AS requests,
  SUM(CASE WHEN cache_outcome = 'hit' THEN 1 ELSE 0 END) AS cache_hits,
  SUM(CASE WHEN attempt > 1 THEN 1 ELSE 0 END) AS retries,
  100.0 * SUM(CASE WHEN cache_outcome = 'hit' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS cache_hit_rate_pct,
  SUM(CASE WHEN attempt > 1 THEN estimated_cost_usd ELSE 0 END) AS retry_cost_usd,
  SUM(estimated_cost_usd) AS total_cost_usd
FROM llm_requests
WHERE timestamp_utc >= now() - INTERVAL '30 days'
GROUP BY model
ORDER BY retry_cost_usd DESC;