Window functions perform calculations across rows related to the current row. Unlike GROUP BY, they don't collapse rows—you keep the detail while adding aggregated data.
Window Function Basics#
1-- Syntax
2SELECT column,
3 window_function() OVER (
4 PARTITION BY partition_column
5 ORDER BY order_column
6 ROWS/RANGE frame_specification
7 ) AS result
8FROM table;
9
10-- Simple example: row number per department
11SELECT
12 name,
13 department,
14 salary,
15 ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
16FROM employees;
17
18-- Result:
19-- name | department | salary | rank
20-- Alice | Engineering| 120000 | 1
21-- Bob | Engineering| 100000 | 2
22-- Carol | Sales | 90000 | 1
23-- David | Sales | 85000 | 2Ranking Functions#
1-- ROW_NUMBER: Unique sequential number
2-- RANK: Same rank for ties, gaps after
3-- DENSE_RANK: Same rank for ties, no gaps
4
5SELECT
6 name,
7 department,
8 salary,
9 ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
10 RANK() OVER (ORDER BY salary DESC) AS rank,
11 DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
12FROM employees;
13
14-- With ties at salary 100000:
15-- name | salary | row_num | rank | dense_rank
16-- Alice | 120000 | 1 | 1 | 1
17-- Bob | 100000 | 2 | 2 | 2
18-- Carol | 100000 | 3 | 2 | 2
19-- David | 90000 | 4 | 4 | 3
20
21-- NTILE: Divide into N buckets
22SELECT
23 name,
24 salary,
25 NTILE(4) OVER (ORDER BY salary) AS quartile
26FROM employees;Aggregate Window Functions#
1-- Running total
2SELECT
3 date,
4 amount,
5 SUM(amount) OVER (ORDER BY date) AS running_total
6FROM orders;
7
8-- Running total per customer
9SELECT
10 customer_id,
11 date,
12 amount,
13 SUM(amount) OVER (
14 PARTITION BY customer_id
15 ORDER BY date
16 ) AS customer_running_total
17FROM orders;
18
19-- Percentage of total
20SELECT
21 product,
22 sales,
23 ROUND(
24 100.0 * sales / SUM(sales) OVER (),
25 2
26 ) AS pct_of_total
27FROM product_sales;
28
29-- Percentage within category
30SELECT
31 category,
32 product,
33 sales,
34 ROUND(
35 100.0 * sales / SUM(sales) OVER (PARTITION BY category),
36 2
37 ) AS pct_of_category
38FROM product_sales;Moving Averages#
1-- 7-day moving average
2SELECT
3 date,
4 revenue,
5 AVG(revenue) OVER (
6 ORDER BY date
7 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
8 ) AS moving_avg_7d
9FROM daily_revenue;
10
11-- 30-day moving average with NULL handling
12SELECT
13 date,
14 revenue,
15 AVG(revenue) OVER (
16 ORDER BY date
17 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
18 ) AS moving_avg_30d,
19 COUNT(*) OVER (
20 ORDER BY date
21 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
22 ) AS days_in_window
23FROM daily_revenue;
24
25-- Centered moving average (for smoothing)
26SELECT
27 date,
28 value,
29 AVG(value) OVER (
30 ORDER BY date
31 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
32 ) AS centered_avg
33FROM measurements;LAG and LEAD#
1-- Compare to previous row
2SELECT
3 date,
4 revenue,
5 LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue,
6 revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day_change
7FROM daily_revenue;
8
9-- Compare to next row
10SELECT
11 date,
12 revenue,
13 LEAD(revenue, 1) OVER (ORDER BY date) AS next_day_revenue
14FROM daily_revenue;
15
16-- With default for NULL
17SELECT
18 date,
19 revenue,
20 LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue_or_zero
21FROM daily_revenue;
22
23-- Month-over-month comparison
24SELECT
25 month,
26 revenue,
27 LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
28 ROUND(
29 100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
30 / LAG(revenue, 1) OVER (ORDER BY month),
31 2
32 ) AS mom_growth_pct
33FROM monthly_revenue;
34
35-- Year-over-year comparison
36SELECT
37 date,
38 revenue,
39 LAG(revenue, 365) OVER (ORDER BY date) AS prev_year_revenue
40FROM daily_revenue;FIRST_VALUE and LAST_VALUE#
1-- First and last in partition
2SELECT
3 department,
4 name,
5 salary,
6 FIRST_VALUE(name) OVER (
7 PARTITION BY department
8 ORDER BY salary DESC
9 ) AS highest_paid,
10 FIRST_VALUE(salary) OVER (
11 PARTITION BY department
12 ORDER BY salary DESC
13 ) AS max_salary
14FROM employees;
15
16-- Compare each row to first/last
17SELECT
18 department,
19 name,
20 salary,
21 salary - FIRST_VALUE(salary) OVER (
22 PARTITION BY department
23 ORDER BY salary DESC
24 ) AS diff_from_max
25FROM employees;
26
27-- LAST_VALUE needs frame specification
28SELECT
29 department,
30 name,
31 salary,
32 LAST_VALUE(salary) OVER (
33 PARTITION BY department
34 ORDER BY salary DESC
35 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
36 ) AS min_salary
37FROM employees;Frame Specifications#
1-- ROWS: Physical rows
2-- RANGE: Logical range (values)
3
4-- All rows from start to current
5ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
6
7-- Current row and all following
8ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
9
10-- 3 rows before and after current
11ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
12
13-- RANGE example: sum all with same date
14SELECT
15 date,
16 amount,
17 SUM(amount) OVER (
18 ORDER BY date
19 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
20 ) AS cumulative_by_date
21FROM orders;
22
23-- Default frame when ORDER BY is specified:
24-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWPractical Examples#
1-- Top 3 products per category
2WITH ranked AS (
3 SELECT
4 category,
5 product,
6 sales,
7 ROW_NUMBER() OVER (
8 PARTITION BY category
9 ORDER BY sales DESC
10 ) AS rank
11 FROM products
12)
13SELECT * FROM ranked WHERE rank <= 3;
14
15-- Identify gaps in sequences
16SELECT
17 id,
18 LAG(id, 1) OVER (ORDER BY id) AS prev_id,
19 id - LAG(id, 1) OVER (ORDER BY id) AS gap
20FROM records
21WHERE id - LAG(id, 1) OVER (ORDER BY id) > 1;
22
23-- Session identification (30 min timeout)
24SELECT
25 user_id,
26 event_time,
27 CASE
28 WHEN event_time - LAG(event_time) OVER (
29 PARTITION BY user_id ORDER BY event_time
30 ) > INTERVAL '30 minutes'
31 THEN 1
32 ELSE 0
33 END AS new_session
34FROM events;
35
36-- Cumulative distribution
37SELECT
38 score,
39 CUME_DIST() OVER (ORDER BY score) AS percentile,
40 PERCENT_RANK() OVER (ORDER BY score) AS percent_rank
41FROM test_scores;
42
43-- De-duplicate keeping latest
44WITH ranked AS (
45 SELECT
46 *,
47 ROW_NUMBER() OVER (
48 PARTITION BY email
49 ORDER BY updated_at DESC
50 ) AS rn
51 FROM users
52)
53SELECT * FROM ranked WHERE rn = 1;Performance Tips#
1-- Use same OVER clause to share computation
2SELECT
3 date,
4 amount,
5 SUM(amount) OVER w AS running_total,
6 AVG(amount) OVER w AS running_avg,
7 COUNT(*) OVER w AS running_count
8FROM orders
9WINDOW w AS (ORDER BY date);
10
11-- Index on partition and order columns
12CREATE INDEX idx_orders_customer_date ON orders(customer_id, date);
13
14-- Filter before window function when possible
15SELECT *
16FROM (
17 SELECT
18 customer_id,
19 date,
20 amount,
21 ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date DESC) AS rn
22 FROM orders
23 WHERE date >= '2024-01-01' -- Filter early
24) t
25WHERE rn = 1;Conclusion#
Window functions are powerful for analytics queries. They let you compare rows, calculate running totals, and rank data without losing row-level detail. Master ROW_NUMBER, LAG/LEAD, and running aggregates—they solve most common analytical needs.