Evaluate RAG retrieval quality by version

WITH cases AS (
  SELECT
    pipeline_version,
    test_case_id,
    COUNT(*) AS records,
    MAX(CASE WHEN relevant_document_retrieved THEN 1
      WHEN relevant_document_retrieved = false THEN 0 END) AS retrieved,
    MAX(CASE WHEN answer_grounded THEN 1
      WHEN answer_grounded = false THEN 0 END) AS grounded,
    MAX(retrieval_latency_ms) AS retrieval_latency_ms,
    MAX(cost_usd) AS cost_usd
  FROM rag_evaluation_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
    AND environment = 'evaluation'
    AND evaluation_run_id = 'release-check-001'
    AND dataset_version = 'faq-v1'
    AND evaluator_version = 'grounding-rubric-v1'
    AND pipeline_version IS NOT NULL AND pipeline_version <> ''
    AND test_case_id IS NOT NULL AND test_case_id <> ''
  GROUP BY pipeline_version, test_case_id
)
SELECT
  pipeline_version,
  SUM(CASE WHEN records = 1 THEN 1 ELSE 0 END) AS test_cases,
  SUM(CASE WHEN records > 1 THEN 1 ELSE 0 END) AS conflicting_test_cases,
  COUNT(CASE WHEN records = 1 THEN retrieved END) AS retrieval_reviewed_cases,
  COUNT(CASE WHEN records = 1 THEN grounded END) AS grounding_reviewed_cases,
  100.0 * SUM(CASE WHEN records = 1 THEN retrieved END)
    / NULLIF(COUNT(CASE WHEN records = 1 THEN retrieved END), 0) AS retrieval_relevance_pct,
  100.0 * SUM(CASE WHEN records = 1 THEN grounded END)
    / NULLIF(COUNT(CASE WHEN records = 1 THEN grounded END), 0) AS grounded_answer_pct,
  AVG(CASE WHEN records = 1 THEN retrieval_latency_ms END) AS avg_retrieval_latency_ms,
  SUM(CASE WHEN records = 1 THEN cost_usd END) AS evaluation_cost_usd
FROM cases
GROUP BY pipeline_version
ORDER BY grounded_answer_pct DESC NULLS LAST,
  retrieval_relevance_pct DESC NULLS LAST, pipeline_version;