Window Functions: Running Totals and Moving Averages

Aggregate functions — SUM, AVG, COUNT, MIN, MAX — become window functions when followed by OVER.

Group totals on every row

SELECT name, department, salary,
  SUM(salary) OVER (PARTITION BY department)                    AS dept_total,
  ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees
ORDER BY department, salary DESC;

+-------+-------------+--------+------------+-------------+
| name  | department  | salary | dept_total | pct_of_dept |
+-------+-------------+--------+------------+-------------+
| Sofia | Engineering | 120000 |     313000 |        38.3 |
| Priya | Engineering |  98000 |     313000 |        31.3 |
| Omar  | Engineering |  95000 |     313000 |        30.4 |
| Chen  | Sales       |  70000 |     134000 |        52.2 |
| Lucia | Sales       |  64000 |     134000 |        47.8 |
| Kofi  | Support     |  52000 |      52000 |       100.0 |
+-------+-------------+--------+------------+-------------+

Each row shows its department's total — and its share of it — without losing the individual rows.

Running totals

Adding ORDER BY inside OVER turns a sum into a running sum:

WITH daily AS (
  SELECT o.ordered_on, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.ordered_on
)
SELECT ordered_on, revenue,
  SUM(revenue) OVER (ORDER BY ordered_on) AS running_total
FROM daily
ORDER BY ordered_on;

+------------+---------+---------------+
| ordered_on | revenue | running_total |
+------------+---------+---------------+
| 2025-01-05 |   160.0 |         160.0 |
| 2025-02-14 |  249.99 |        409.99 |
| 2025-02-20 |   234.5 |        644.49 |
| 2025-03-01 |  583.98 |       1228.47 |
| 2025-04-02 |      89 |       1317.47 |
| 2025-04-10 |     241 |       1558.47 |
| 2025-04-22 |   179.7 |       1738.17 |
+------------+---------+---------------+

Frames: moving averages

A frame says exactly which rows around the current one to include:

-- standalone
CREATE TABLE visits (day INTEGER, visitors INTEGER);
INSERT INTO visits VALUES (1, 100), (2, 120), (3, 90), (4, 150), (5, 130), (6, 170);

SELECT day, visitors,
  ROUND(AVG(visitors) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM visits
ORDER BY day;

+-----+----------+--------------+
| day | visitors | moving_avg_3 |
+-----+----------+--------------+
|   1 |      100 |        100.0 |
|   2 |      120 |        110.0 |
|   3 |       90 |        103.3 |
|   4 |      150 |        120.0 |
|   5 |      130 |        123.3 |
|   6 |      170 |        150.0 |
+-----+----------+--------------+

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current day and the two before it — a 3-day moving average, which smooths out daily noise. (The first two days average fewer rows.)

Common frames:

A subtle default

With ORDER BY and no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which includes all rows tied with the current one. If two rows share a date, both get the same running total. Specify ROWS explicitly when you want one row at a time.