Which customers drive your revenue — and which are growing fastest?

WITH customer_revenue AS (
  SELECT
    c.display_name                                 AS customer,
    SUM(CASE
      WHEN i.txn_date >= DATE_TRUNC('year', CURRENT_DATE)
      THEN il.amount ELSE 0
    END)                                           AS current_year,
    SUM(CASE
      WHEN i.txn_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
       AND i.txn_date < DATE_TRUNC('year', CURRENT_DATE)
      THEN il.amount ELSE 0
    END)                                           AS prior_year
  FROM qbo_invoices i
  JOIN qbo_invoice_line_items il ON il.invoice_id = i.id
  JOIN qbo_customers c ON c.id = i.customer_id
  WHERE i.txn_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
  GROUP BY c.display_name
)
SELECT
  customer,
  ROUND(current_year::numeric, 2)                 AS current_year_revenue,
  ROUND(prior_year::numeric, 2)                   AS prior_year_revenue,
  ROUND((current_year - prior_year)::numeric, 2)  AS change,
  ROUND(
    (current_year - prior_year)
    / NULLIF(prior_year, 0) * 100, 1
  )                                                AS yoy_growth_pct,
  ROUND(current_year * 100.0
    / NULLIF(SUM(current_year) OVER (), 0), 1)    AS pct_of_total
FROM customer_revenue
WHERE current_year > 0
ORDER BY current_year DESC
LIMIT 25