Named Windows

Named Windows

Named Windows and Practical Reporting Patterns

A WINDOW clause after HAVING names a window, and OVER w reuses it. OVER (w ...) may add a frame but not a second ORDER BY (error 3583). A monthly dashboard in one statement:

Running total, three-month moving average and cumulative share of revenueSQL

SELECT month, revenue, SUM(revenue) OVER w AS running,
       ROUND(AVG(revenue) OVER (w ROWS 2 PRECEDING), 2)          AS avg_3m,
       ROUND(100 * SUM(revenue) OVER w / SUM(revenue) OVER (), 1) AS pct_to_date
FROM monthly_revenue WINDOW w AS (ORDER BY month);

Output

+---------+---------+---------+--------+-------------+
| month   | revenue | running | avg_3m | pct_to_date |
+---------+---------+---------+--------+-------------+
| 2026-03 |  153.37 |  153.37 | 153.37 |        25.2 |
| 2026-04 |   49.00 |  202.37 | 101.19 |        33.3 |
| 2026-05 |   86.50 |  288.87 |  96.29 |        47.5 |
| 2026-06 |  128.80 |  417.67 |  88.10 |        68.7 |
| 2026-07 |   50.00 |  467.67 |  88.43 |        76.9 |
| 2026-08 |   86.50 |  554.17 |  88.43 |        91.1 |
| 2026-09 |   53.99 |  608.16 |  63.50 |       100.0 |
+---------+---------+---------+--------+-------------+
7 rows in set (0.001 sec)

The default RANGE frame is safe here because months are unique. Other reports reuse the shapes: top N per group ranks in a CTE and filters outside (Ranking Functions); period over period is LAG; deduplication deletes WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS n FROM t) AS d WHERE n > 1), since DELETE cannot use a window itself.

MENU