Window Functions (ROW_NUMBER, RANK, DENSE_RANK)
Window functions compute aggregate-like values across rows related to the current row - without collapsing rows into groups. They are the most powerful feature for analytics queries: ranking, running totals, moving averages, and row numbering.
Window Function Fundamentals
- Syntax: function() OVER (PARTITION BY col ORDER BY col)
- Window functions run AFTER WHERE, GROUP BY, HAVING - they see the filtered result set
- Do NOT collapse rows - unlike GROUP BY, all rows remain in output
- ROW_NUMBER() - sequential integer, unique per window (1, 2, 3...)
- RANK() - rank with gaps for ties (1, 2, 2, 4...)
- DENSE_RANK() - rank without gaps for ties (1, 2, 2, 3...)
- NTILE(n) - divide rows into n buckets
- PERCENT_RANK() - relative rank as 0-1
- SUM() OVER, AVG() OVER, COUNT() OVER - running/window aggregates
Ranking Examples on ecommerce_db
-- Rank products by price (most expensive first)
SELECT
name,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
RANK() OVER (ORDER BY price DESC) AS rank,
DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;
-- Result (if two products had same price):
-- name | price | row_num | rank | dense_rank
-- Laptop | 999.99 | 1 | 1 | 1
-- Phone | 599.99 | 2 | 2 | 2
-- Headphones | 89.99 | 3 | 3 | 3
-- Rank orders by amount per user (PARTITION BY)
SELECT
user_id,
id AS order_id,
total_amount,
RANK() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS rank_by_user
FROM orders;
-- Each user gets their own ranking (reset at each user_id group)
-- Running total of payments
SELECT
paid_at,
amount,
SUM(amount) OVER (ORDER BY paid_at) AS running_total
FROM payments
WHERE paid_at IS NOT NULL
ORDER BY paid_at;
-- Top-N per group: get top 1 order per user
SELECT * FROM (
SELECT *, RANK() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS rk
FROM orders
) ranked
WHERE rk = 1;Quick Quiz
Tip
Tip
Practice Window Functions ROWNUMBER RANK DENSERANK in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Window functions = aggregate without GROUP BY. Calculate across related rows.
Common Mistake
Warning
A common mistake with Window Functions ROWNUMBER RANK DENSERANK is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Window Functions ROWNUMBER RANK DENSERANK from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Window functions compute aggregate-like values across rows related to the current row - without collapsing rows into groups.
- Syntax: function() OVER (PARTITION BY col ORDER BY col)
- Window functions run AFTER WHERE, GROUP BY, HAVING - they see the filtered result set
- Do NOT collapse rows - unlike GROUP BY, all rows remain in output