📊 SQL One-Liner: Rolling Sum & Cumulative Totals

Cumulative and rolling sums are fundamental in time series analysis and business reporting.

Long Way: Uses subqueries, repeatedly scanning the table → slow.

Shortcut One-Liner: Uses SUM() OVER to efficiently calculate totals.

Rolling Sum: Adds flexibility with ROWS BETWEEN … for sliding windows.

👉 Real-world use cases:

Running total of sales/revenue.

Cumulative signups or active users.

Rolling transaction volumes (last 7 days, last 30 days).

Queries:

✅ Long Way (Correlated Subquery for Cumulative Sales):
SELECT t1.order_date,
(SELECT SUM(t2.amount)
FROM orders t2
WHERE t2.order_date v= t1.order_date) AS cumulative_sales
FROM orders t1
ORDER BY t1.order_date;
Explanation:

For each row, it runs a subquery summing all prior rows.

Works fine for small data but becomes very slow on large datasets.

✅ Shortcut One-Liner (Window Function):
SELECT order_date,
SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM orders
ORDER BY order_date;
Explanation:

SUM() OVER (ORDER BY order_date) creates a running total from the start up to the current row.

No subqueries, no joins — just one scan with a window function.

✅ Rolling Sum in a Fixed Window (e.g., last 3 days):
SELECT order_date,
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum
FROM orders
ORDER BY order_date;
Explanation:

Instead of summing from the start, this defines a sliding window of the current day + last 2.

Perfect for short-term trends.

Видео 📊 SQL One-Liner: Rolling Sum & Cumulative Totals канала CodeVisium