Naming a window in SQL

Repeating the same OVER clause four times is how a typo gets in.

SELECT
    month,
    revenue,
    SUM(revenue) OVER rolling AS rolling_sum,
    AVG(revenue) OVER rolling AS rolling_avg,
    MAX(revenue) OVER rolling AS rolling_peak
FROM monthly_revenue
WINDOW rolling AS (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
ORDER BY month;

How it works

1. The WINDOW clause names a window definition once.
2. Every function then refers to it by name.
3. WINDOW sits after HAVING and before the final ORDER BY.

Keywords and builtins used here

• AND
• AS
• AVG
• BETWEEN
• BY
• CURRENT
• FROM
• MAX
• ORDER
• ROW
• ROWS
• SELECT
• SUM
• month

The run, in numbers

Lines

12

Characters to type

263

Tokens

52

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 33 seconds.

Type this snippet

Step 6 of 9 in Window functions, step 12 of 24 in Analytics & reporting.

← Previous Next →