What is renewal rate, and how do you calculate it?

-- The denominator is contracts whose term ENDED in the quarter, not
-- contracts that happened to renew in it. Bucketing by close date lets
-- late renewals wander between quarters and flatters every miss.
WITH due AS (
  SELECT
    date_trunc('quarter', c.term_end_date)::date AS quarter,
    c.contract_id,
    c.arr_usd,
    r.renewed_at,
    r.renewal_arr_usd
  FROM contracts c
  LEFT JOIN contract_renewals r
    ON r.previous_contract_id = c.contract_id
   -- 30-day grace window: a late renewal still counts, a deal six
   -- months later is a winback, not a renewal.
   AND r.renewed_at <= c.term_end_date + INTERVAL '30 days'
  WHERE c.term_end_date >=
        date_trunc('quarter', CURRENT_DATE) - INTERVAL '2 years'
    AND c.term_end_date < date_trunc('quarter', CURRENT_DATE)
    -- Don't score a contract as a miss while its grace window is still
    -- open: a deal due last week could yet renew within the 30 days.
    AND c.term_end_date + INTERVAL '30 days' < CURRENT_DATE
)
SELECT
  quarter,
  COUNT(*) AS contracts_due,
  COUNT(renewed_at) AS contracts_renewed,
  ROUND(
    100.0 * COUNT(renewed_at) / NULLIF(COUNT(*), 0), 1
  ) AS logo_renewal_rate_pct,
  -- Cap each renewal at its prior ARR so upsell at renewal time
  -- cannot push the gross rate past 100%.
  ROUND(
    100.0 * SUM(LEAST(COALESCE(renewal_arr_usd, 0), arr_usd))
    / NULLIF(SUM(arr_usd), 0), 1
  ) AS gross_renewal_rate_pct
FROM due
GROUP BY quarter
ORDER BY quarter;