Daily revenue, running total and day-over-day change in SQL

-- ======================================================================
-- Q: Compute daily revenue, the running total for the month and the change from
-- the previous day.
--
-- The CTE does the per-day aggregate, and then the window functions work on the
-- small result. I declare ROWS BETWEEN explicitly because the default frame is
-- RANGE, which gives "odd" sums when there are duplicate values in the ORDER BY.
-- NULLIF(x, 0) avoids division by zero. Days without sales are missing, so if a
-- continuous series is needed I LEFT JOIN to generate_series.
-- ======================================================================

WITH daily AS (
    SELECT date_trunc('day', created_at)::date AS day,
           SUM(total_cents)                    AS revenue
    FROM orders
    WHERE status = 'paid'
      AND created_at >= date_trunc('month', now())
    GROUP BY 1
)
SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY day)                            AS diff_prev_day,
       ROUND(100.0 * (revenue - LAG(revenue) OVER w)
             / NULLIF(LAG(revenue) OVER w, 0), 1)                            AS pct_change
FROM daily
WINDOW w AS (ORDER BY day)
ORDER BY day;