Window Functions
ROW_NUMBER, RANK, LAG, LEAD, and PARTITION BY
Skills taught: sql.window-functions, sql.partition-by, sql.row-number, sql.rank, sql.lag-lead
Ranking functions
-- ROW_NUMBER: sequential numbers within partition
SELECT
name, category, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;
-- RANK: ties get the same rank, with gaps
-- DENSE_RANK: ties get the same rank, no gaps
SELECT
name, total,
RANK() OVER (ORDER BY total DESC) AS rank_with_gap,
DENSE_RANK() OVER (ORDER BY total DESC) AS rank_no_gap
FROM orders;
-- Top product per category (using ROW_NUMBER)
-- (CTEs will be explained in the next lesson)
SELECT name, category, price
FROM (
SELECT
name, category, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
)
WHERE rn = 1;
LAG, LEAD, and running totals
-- LAG: value from the previous row
-- LEAD: value from the next row
SELECT
id, total, ordered_at,
LAG(total) OVER (ORDER BY ordered_at) AS prev_order_total,
LEAD(total) OVER (ORDER BY ordered_at) AS next_order_total,
total - LAG(total) OVER (ORDER BY ordered_at) AS change_from_prev
FROM orders;
-- Running total (cumulative sum)
SELECT
ordered_at,
total,
SUM(total) OVER (ORDER BY ordered_at) AS running_total
FROM orders;
-- Running total per user
SELECT
user_id,
total,
ordered_at,
SUM(total) OVER (PARTITION BY user_id ORDER BY ordered_at) AS user_running_total
FROM orders;
-- Average + count alongside individual rows
-- (without GROUP BY — rows are NOT collapsed)
SELECT
name, price, category,
ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg,
COUNT(*) OVER (PARTITION BY category) AS category_count
FROM products;
Frequently Asked Questions
What is PARTITION BY in a window function?
PARTITION BY divides rows into groups (like GROUP BY), but rows stay separate — they're not collapsed. The window function is computed independently for each partition. ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) gives separate 1,2,3 numbering within each category.