Calculate Running Totals or Moving Averages in SQL

SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_3_days
FROM orders
ORDER BY order_date;

How it works: This SQL snippet demonstrates the power of window functions to calculate running totals and moving averages. SUM(total_amount) OVER (ORDER BY order_date) computes a cumulative sum of total_amount based on the order date. The AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) calculates a 3-day moving average, considering the current row and the two preceding rows, providing smoothed data for trend analysis.