Lesson 6: Keep row detail while calculating across rows
A window function uses an OVER clause. PARTITION BY starts a calculation for each group; ORDER BY defines sequence. Unlike GROUP BY, it keeps one output row for each input row.
SELECT order_id, customer_id, order_date, total,
SUM(total) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_customer_total
FROM orders;Window function reference
11 patterns shown
| Function or clause | Purpose | Example |
|---|---|---|
| ROW_NUMBER | Unique sequential number within a partition. | ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) |
| RANK | Ranking with gaps after ties. | RANK() OVER (ORDER BY total DESC) |
| DENSE_RANK | Ranking without gaps after ties. | DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) |
| NTILE | Divides ordered rows into buckets. | NTILE(4) OVER (ORDER BY total) |
| LAG | Reads a preceding row in the window order. | LAG(total) OVER (PARTITION BY customer_id ORDER BY order_date) |
| LEAD | Reads a following row in the window order. | LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) |
| FIRST_VALUE | Returns the first value in the current frame. | FIRST_VALUE(total) OVER (PARTITION BY customer_id ORDER BY order_date) |
| LAST_VALUE | Returns the last value in the current frame; specify a frame ending at UNBOUNDED FOLLOWING for partition last. | LAST_VALUE(total) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) |
| SUM / AVG OVER | Windowed aggregate without grouping away detail rows. | AVG(total) OVER (PARTITION BY customer_id) |
| PARTITION BY | Restarts calculations for each group. | OVER (PARTITION BY region) |
| Window frame | Defines which ordered rows contribute to a result. | ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates a seven-row window. |
Make window results deterministic
Add a unique tie-breaker to ORDER BY when row sequence matters. Explicitly select ROWS frames for running totals; default frames can include peer rows and change results when sort values tie. Use a subquery or CTE to filter a window result because window functions are evaluated after WHERE.
Progress stays in this browser.