|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/sql-window-functions

$cat docs/sql-window-functions.md

updated Recently·45 min read·published

SQL Window Functions

◆SQL◆Databases◆Advanced◆Advanced🎯Free Tools

Introduction

Window functions perform calculations across a set of rows related to the current row. Unlike aggregate functions, window functions do not collapse rows — they return a value for every row in the result set. This makes them incredibly powerful for analytics, ranking, and time-series analysis.

Window functions use the OVER clause to define the window (set of rows) to operate on. You can partition rows into groups, order them, and define a frame (subset of rows) for each calculation.

ROW_NUMBER, RANK, and DENSE_RANK

These functions assign numbers to rows based on their order. The key difference is how they handle ties (rows with the same values).

row-number-rank.sql

SQL

1	-- ROW_NUMBER: Unique number for each row (no ties)
2	SELECT
3	name,
4	salary,
5	ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
6	FROM employees;
7
8	-- RANK: Same rank for ties, gaps after ties
9	-- If two people have the same salary, they get the same rank
10	-- The next rank skips numbers (1, 2, 2, 4)
11	SELECT
12	name,
13	salary,
14	RANK() OVER (ORDER BY salary DESC) AS rank
15	FROM employees;
16
17	-- DENSE_RANK: Same rank for ties, no gaps
18	-- If two people have the same salary, they get the same rank
19	-- The next rank is consecutive (1, 2, 2, 3)
20	SELECT
21	name,
22	salary,
23	DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
24	FROM employees;
25
26	-- Compare all three side by side
27	SELECT
28	name,
29	salary,
30	ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
31	RANK() OVER (ORDER BY salary DESC) AS rank,
32	DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
33	FROM employees
34	ORDER BY salary DESC;
35
36	-- PARTITION BY: Rank within each department
37	SELECT
38	name,
39	department,
40	salary,
41	ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
42	FROM employees;
43
44	-- Find top 3 earners in each department
45	SELECT *
46	FROM (
47	SELECT
48	name,
49	department,
50	salary,
51	ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
52	FROM employees
53	) ranked
54	WHERE dept_rank <= 3;

ℹ

info

Use ROW_NUMBER when you need a unique identifier for each row. Use RANK when you want gaps after ties. Use DENSE_RANK when you want consecutive ranks without gaps.

LAG and LEAD — Accessing Previous/Next Rows

LAG accesses data from a previous row in the same result set. LEAD accesses data from a subsequent row. They are essential for time-series analysis and comparing consecutive values.

lag-lead.sql

SQL

1	-- LAG: Get previous row's value
2	SELECT
3	date,
4	revenue,
5	LAG(revenue) OVER (ORDER BY date) AS prev_day_revenue
6	FROM daily_revenue;
7
8	-- LEAD: Get next row's value
9	SELECT
10	date,
11	revenue,
12	LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
13	FROM daily_revenue;
14
15	-- Calculate day-over-day change
16	SELECT
17	date,
18	revenue,
19	LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
20	revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
21	ROUND(
22	(revenue - LAG(revenue) OVER (ORDER BY date))
23	/ NULLIF(LAG(revenue) OVER (ORDER BY date), 0) * 100,
24	2
25	) AS growth_pct
26	FROM daily_revenue;
27
28	-- LAG with offset and default value
29	SELECT
30	date,
31	revenue,
32	LAG(revenue, 7, 0) OVER (ORDER BY date) AS prev_week_revenue
33	FROM daily_revenue;
34
35	-- Compare with same day last week
36	SELECT
37	date,
38	revenue,
39	LAG(revenue, 7) OVER (ORDER BY date) AS same_day_last_week
40	FROM daily_revenue;
41
42	-- LAG with PARTITION BY: Compare within each product
43	SELECT
44	product_id,
45	date,
46	sales,
47	LAG(sales) OVER (PARTITION BY product_id ORDER BY date) AS prev_day_sales
48	FROM product_sales;
49
50	-- Find users who upgraded their plan
51	SELECT
52	user_id,
53	plan,
54	created_at,
55	LAG(plan) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_plan,
56	CASE
57	WHEN plan != LAG(plan) OVER (PARTITION BY user_id ORDER BY created_at)
58	THEN 'Plan changed'
59	ELSE 'No change'
60	END AS status
61	FROM subscriptions;

📝

note

LAG and LEAD return NULL for the first/last row when there is no previous/next row. Use the default value parameter to handle this: LAG(value, offset, default).

FIRST_VALUE and LAST_VALUE

FIRST_VALUE returns the first value in the window frame. LAST_VALUE returns the last value. These are useful for comparing each row against the first or last row in a partition.

first-last-value.sql

SQL

1	-- FIRST_VALUE: Compare each row to the first row in partition
2	SELECT
3	name,
4	department,
5	salary,
6	FIRST_VALUE(salary) OVER (
7	PARTITION BY department
8	ORDER BY salary DESC
9	) AS dept_max_salary,
10	salary - FIRST_VALUE(salary) OVER (
11	PARTITION BY department
12	ORDER BY salary DESC
13	) AS diff_from_max
14	FROM employees;
15
16	-- LAST_VALUE: Compare each row to the last row in partition
17	-- Note: Need to define frame to see the actual last row
18	SELECT
19	name,
20	department,
21	salary,
22	LAST_VALUE(salary) OVER (
23	PARTITION BY department
24	ORDER BY salary DESC
25	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
26	) AS dept_min_salary
27	FROM employees;
28
29	-- Calculate percentage of max salary in department
30	SELECT
31	name,
32	department,
33	salary,
34	ROUND(
35	salary / FIRST_VALUE(salary) OVER (
36	PARTITION BY department
37	ORDER BY salary DESC
38	) * 100,
39	2
40	) AS pct_of_max
41	FROM employees;
42
43	-- Compare to first and last order dates
44	SELECT
45	order_id,
46	created_at,
47	FIRST_VALUE(created_at) OVER (
48	PARTITION BY user_id
49	ORDER BY created_at
50	) AS first_order_date,
51	LAST_VALUE(created_at) OVER (
52	PARTITION BY user_id
53	ORDER BY created_at
54	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
55	) AS last_order_date
56	FROM orders;

Aggregate Window Functions

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) can also be used as window functions. Unlike GROUP BY, they do not collapse rows — they return the aggregate value for every row.

aggregate-window.sql

SQL

1	-- SUM: Running total
2	SELECT
3	date,
4	revenue,
5	SUM(revenue) OVER (ORDER BY date) AS running_total
6	FROM daily_revenue;
7
8	-- SUM with PARTITION BY: Running total per product
9	SELECT
10	product_id,
11	date,
12	sales,
13	SUM(sales) OVER (PARTITION BY product_id ORDER BY date) AS product_running_total
14	FROM product_sales;
15
16	-- AVG: Moving average (last 7 days)
17	SELECT
18	date,
19	revenue,
20	AVG(revenue) OVER (
21	ORDER BY date
22	ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
23	) AS moving_avg_7d
24	FROM daily_revenue;
25
26	-- COUNT: Running count
27	SELECT
28	date,
29	COUNT(*) OVER (ORDER BY date) AS cumulative_count
30	FROM daily_revenue;
31
32	-- MIN/MAX: Compare to overall min/max
33	SELECT
34	name,
35	salary,
36	MIN(salary) OVER () AS company_min_salary,
37	MAX(salary) OVER () AS company_max_salary,
38	salary - MIN(salary) OVER () AS above_min
39	FROM employees;
40
41	-- MIN/MAX with PARTITION BY: Compare to department min/max
42	SELECT
43	name,
44	department,
45	salary,
46	MIN(salary) OVER (PARTITION BY department) AS dept_min,
47	MAX(salary) OVER (PARTITION BY department) AS dept_max,
48	salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
49	FROM employees;
50
51	-- Percentage of total
52	SELECT
53	product_id,
54	sales,
55	ROUND(
56	sales / SUM(sales) OVER () * 100,
57	2
58	) AS pct_of_total
59	FROM product_sales;
60
61	-- Percentage of partition total
62	SELECT
63	product_id,
64	category,
65	sales,
66	ROUND(
67	sales / SUM(sales) OVER (PARTITION BY category) * 100,
68	2
69	) AS pct_of_category
70	FROM product_sales;

Window Frames

Window frames define which rows are included in the calculation for each row. You can use ROWS (physical rows) or RANGE (logical range based on values).

window-frames.sql

SQL

1	-- ROWS frame: Physical rows relative to current row
2	SELECT
3	date,
4	revenue,
5	SUM(revenue) OVER (
6	ORDER BY date
7	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
8	) AS sum_last_3_days
9	FROM daily_revenue;
10
11	-- ROWS frame: Centered window
12	SELECT
13	date,
14	revenue,
15	AVG(revenue) OVER (
16	ORDER BY date
17	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
18	) AS centered_avg
19	FROM daily_revenue;
20
21	-- ROWS frame: All rows from start to current
22	SELECT
23	date,
24	revenue,
25	SUM(revenue) OVER (
26	ORDER BY date
27	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
28	) AS cumulative_sum
29	FROM daily_revenue;
30
31	-- ROWS frame: All rows in partition
32	SELECT
33	date,
34	revenue,
35	SUM(revenue) OVER (
36	ORDER BY date
37	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
38	) AS total_sum
39	FROM daily_revenue;
40
41	-- RANGE frame: Logical range (same values treated as one)
42	SELECT
43	date,
44	revenue,
45	SUM(revenue) OVER (
46	ORDER BY date
47	RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
48	) AS sum_last_7_days
49	FROM daily_revenue;
50
51	-- GROUPS frame: Groups of rows with same value (PostgreSQL 11+)
52	SELECT
53	date,
54	revenue,
55	SUM(revenue) OVER (
56	ORDER BY date
57	GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
58	) AS sum_last_3_groups
59	FROM daily_revenue;
60
61	-- EXCLUDE: Exclude specific rows from frame
62	SELECT
63	date,
64	revenue,
65	AVG(revenue) OVER (
66	ORDER BY date
67	ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
68	EXCLUDE CURRENT ROW
69	) AS avg_excluding_self
70	FROM daily_revenue;

⚠

warning

The default frame when using ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This can produce unexpected results with duplicates. Always specify ROWS explicitly for clarity.

Practical Examples

practical-examples.sql

SQL

1	-- 1. Top N per group (using ROW_NUMBER)
2	SELECT *
3	FROM (
4	SELECT
5	name,
6	department,
7	salary,
8	ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
9	FROM employees
10	) ranked
11	WHERE rank <= 3;
12
13	-- 2. Deduplication (keep first occurrence)
14	DELETE FROM users
15	WHERE id NOT IN (
16	SELECT MIN(id)
17	FROM users
18	GROUP BY email
19	);
20
21	-- Or using ROW_NUMBER
22	WITH ranked AS (
23	SELECT
24	id,
25	ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
26	FROM users
27	)
28	DELETE FROM users
29	WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
30
31	-- 3. Gaps and islands (find consecutive date ranges)
32	SELECT
33	MIN(date) AS start_date,
34	MAX(date) AS end_date,
35	COUNT(*) AS days_count
36	FROM (
37	SELECT
38	date,
39	date - (ROW_NUMBER() OVER (ORDER BY date))::int AS grp
40	FROM daily_sales
41	) t
42	GROUP BY grp
43	ORDER BY start_date;
44
45	-- 4. Session detection (group events within 30 minutes)
46	SELECT
47	user_id,
48	event_time,
49	SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
50	FROM (
51	SELECT
52	user_id,
53	event_time,
54	CASE
55	WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) > INTERVAL '30 minutes'
56	THEN 1
57	ELSE 0
58	END AS new_session
59	FROM events
60	) t;
61
62	-- 5. Percentile ranking
63	SELECT
64	name,
65	salary,
66	PERCENT_RANK() OVER (ORDER BY salary) AS percentile,
67	NTILE(4) OVER (ORDER BY salary) AS quartile
68	FROM employees;
69
70	-- 6. Cumulative distribution
71	SELECT
72	name,
73	salary,
74	CUME_DIST() OVER (ORDER BY salary) AS cume_dist
75	FROM employees;

Summary

Function	Purpose	Example
ROW_NUMBER	Unique number for each row	ROW_NUMBER() OVER (ORDER BY salary DESC)
RANK	Rank with gaps after ties	RANK() OVER (ORDER BY salary DESC)
DENSE_RANK	Rank without gaps	DENSE_RANK() OVER (ORDER BY salary DESC)
LAG	Previous row value	LAG(revenue) OVER (ORDER BY date)
LEAD	Next row value	LEAD(revenue) OVER (ORDER BY date)
FIRST_VALUE	First value in partition	FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC)
LAST_VALUE	Last value in partition	LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC)
SUM/AVG/COUNT	Aggregate over window	SUM(sales) OVER (PARTITION BY product ORDER BY date)

X GitHub YouTube Instagram Facebook Discord Slack Telegram Buy me a coffee Ko-fi Sponsor