SQL
Calculate Running Totals with SQL Window Functions
Utilize SQL window functions like SUM() OVER() to calculate running totals or cumulative sums efficiently within your datasets, useful for financial reports or trend analysis.
SELECT
order_date,
customer_id,
order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total_per_customer,
SUM(order_amount) OVER (ORDER BY order_date) AS overall_running_total
FROM
CustomerOrders
ORDER BY
customer_id, order_date;
How it works: SQL window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not group rows into a single output row; instead, they return a value for each row. Here, `SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date)` calculates a running total of `order_amount` for each `customer_id`, ordered by `order_date`. The `PARTITION BY` clause divides the result set into partitions (e.g., per customer), and `ORDER BY` defines the order of rows within each partition.