SQL Running Totals and Cumulative Metrics

-- Running total per customer
SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
FROM orders;

-- Cumulative distinct users per day
WITH daily_new AS (
    SELECT
        DATE(created_at) AS dt,
        COUNT(DISTINCT user_id) AS new_users
    FROM signups
    GROUP BY DATE(created_at)
)
SELECT
    dt,
    new_users,
    SUM(new_users) OVER (ORDER BY dt) AS cumulative_users
FROM daily_new;

-- Percent of total revenue per product
SELECT
    product,
    SUM(amount) AS revenue,
    ROUND(
        100.0 * SUM(amount) / SUM(SUM(amount)) OVER (),
        2
    ) AS pct_of_total,
    SUM(SUM(amount)) OVER (
        ORDER BY SUM(amount) DESC
    ) AS cumulative_revenue
FROM orders
GROUP BY product
ORDER BY revenue DESC;

-- Month-over-month growth rate
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        1
    ) AS growth_pct
FROM monthly;