Window Functions

~14 min · queries, windows

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Aggregates without collapsing

GROUP BY collapses rows into summaries. Window functions compute across rows without collapsing — every row keeps its identity and gains a calculated value. Think of it as writing notes in the margin of each row.

The OVER clause

SUM(total) OVER (PARTITION BY user_id ORDER BY placed_at): for each row, sum the total of every row in the same user partition up to the current row (running total). Without ORDER BY, you'd get the per-partition total on every row. PARTITION BY is like GROUP BY but doesn't collapse.

Frame clauses

For sliding windows like moving averages, add a frame: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. This computes over the current row plus the previous six — a 7-row moving average.

Code

Running totals and per-customer averages·sql

-- Running total per customer
SELECT u.name, o.placed_at, o.total,
       SUM(o.total) OVER (
         PARTITION BY u.id
         ORDER BY o.placed_at
       ) AS running_total
FROM   users u
JOIN   orders o ON o.user_id = u.id
ORDER  BY u.name, o.placed_at;

Difference from per-customer average·sql

SELECT u.name, o.total,
       AVG(o.total) OVER (PARTITION BY u.id)         AS customer_avg,
       o.total - AVG(o.total) OVER (PARTITION BY u.id) AS diff_from_avg
FROM   users u
JOIN   orders o ON o.user_id = u.id;

Moving 7-day average·sql

SELECT date, revenue,
       AVG(revenue) OVER (
         ORDER BY date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_revenue
ORDER BY date;

Named window for reuse·sql

SELECT id, customer_id, total,
       SUM(total)   OVER w  AS running_total,
       AVG(total)   OVER w  AS running_avg,
       COUNT(*)     OVER w  AS running_count
FROM   orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at);

External links

PostgreSQL Window Functions

Exercise

Write a query that returns each user's most recent 3 orders along with a running total of their lifetime spend. Use a window function for the running total and ROW_NUMBER (next lesson) to limit per user.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousHAVING: Filtering After Aggregation Next →ROW_NUMBER, RANK, and CTEs

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.