WITH daily_rev AS (
  SELECT
    order_date::DATE AS d,
    SUM(total) AS revenue
  FROM orders WHERE status = 'completed'
  GROUP BY order_date::DATE
)
SELECT
  d AS date,
  revenue,
  LAG(revenue, 365) OVER (ORDER BY d) AS same_day_last_year,
  ROUND(
    (revenue - LAG(revenue, 365) OVER (ORDER BY d))
    / NULLIF(LAG(revenue, 365) OVER (ORDER BY d), 0) * 100, 2
  ) AS yoy_daily_pct
FROM daily_rev
ORDER BY d DESC;