Ever tried to calculate the average salary per department while keeping every employee row visible? With GROUP BY you lose the rows. With a subquery you add complexity and worse performance. That’s when you need PostgreSQL window functions: OVER, PARTITION BY, ROW_NUMBER. Here at Meteora Web, we use them daily to generate reports from client databases without multiplying joins or downloading data into memory. In this guide you’ll learn the why, the how, and the tricks that saved us more than once.
Why do PostgreSQL window functions solve problems that GROUP BY cannot?
Imagine comparing monthly revenue with a 3‑month moving average. GROUP BY gives you one aggregated row per month — you lose the per‑row comparison with the previous or next row. Window functions operate on a set of rows (the “window”) while keeping the original rows. Result: you can compute running totals, rankings, differences from the maximum, all in the same query.
Real example: an e‑commerce client (the kind we’ve been following since 2017) needed a product ranking per category. One window function query. A subquery? Nested joins, slow.
Sponsored Protocol
Minimum syntax you must know
SELECT column, function() OVER (
[PARTITION BY group_column]
[ORDER BY sort_column]
[ROWS BETWEEN ...]
) FROM table;Every part has a role. PARTITION BY splits data into groups (like GROUP BY but without collapsing rows). ORDER BY defines the order inside each group. ROWS BETWEEN sets the frame boundaries (default: from group start to current row with ORDER BY, or whole group without).
How do OVER and PARTITION BY work in analytical queries?
OVER tells the engine: “calculate this function over a window of rows”. PARTITION BY narrows the window to a subset. Without PARTITION BY, the window is the whole table. With PARTITION BY department, each department has its own independent window.
Common mistake: using PARTITION BY when you need the whole dataset. Example: calculating each sale’s percentage of the global total. In that case no PARTITION BY, just OVER().
Sponsored Protocol
SELECT
month,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS percent_of_total
FROM monthly_sales;We used this exact technique for a clothing store client (remember? we managed their ERP). They wanted each month’s weight on the whole season. PARTITION BY season would have given percentage within the season, not the total. OVER() without PARTITION was the right call.
ROW_NUMBER, RANK, and DENSE_RANK — what’s the practical difference?
These three functions assign progressive numbers, but behave differently on ties.
- ROW_NUMBER(): assigns a unique number to every row. On ties, order is arbitrary (depends on ORDER BY and physical tuple).
- RANK(): on ties, same number, but the next one is skipped (e.g. 1,1,3).
- DENSE_RANK(): like RANK, but without gaps (1,1,2).
When to use them: ROW_NUMBER for pagination or deduplication (keep only one row per group). RANK for sales rankings where you want to see how many are tied for first. DENSE_RANK for gapless leaderboards (e.g. top 20 of a tournament).
Sponsored Protocol
SELECT
product,
category,
sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dense_rank
FROM product_sales;How to use ROW_NUMBER for deduplication and efficient pagination?
One of the most powerful patterns: remove duplicates from a table while keeping only the first occurrence per group.
WITH dedup AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
DELETE FROM dedup WHERE rn > 1;Note: in PostgreSQL you can use a modifiable CTE (DELETE FROM dedup). We solved a dirty data problem in a small business ERP with this pattern – 30,000 duplicate rows removed in under a second.
Pagination: instead of OFFSET (which scans all skipped rows), use ROW_NUMBER to select a precise range:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY date DESC) AS rn
FROM articles
) sub
WHERE rn BETWEEN 21 AND 40;Faster than OFFSET on large datasets, though not as fast as keyset pagination. For 95% of SMEs it works perfectly.
Sponsored Protocol
Which frame and ORDER BY combinations give the right results?
Window functions with frames (ROWS BETWEEN) let you compute moving averages, cumulative totals, differences from the first or last value in the group.
Running total
SELECT
month,
revenue,
SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total
FROM monthly_revenue;ROWS UNBOUNDED PRECEDING starts from the first row of the group (or the whole table if no PARTITION BY) up to the current row. For a 3‑month moving average:
SELECT
month,
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM monthly_revenue;Caution with ORDER BY: if you use ROWS BETWEEN, ORDER BY is mandatory. If ORDER BY is not unique, PostgreSQL may add a default column for stability. We always recommend specifying an ORDER BY with a unique column (e.g. id, date+time) to avoid non‑deterministic results.
Sponsored Protocol
What to do now — from theory to your next query
1. Open your PostgreSQL database (pgAdmin or psql) and create a test table with sales data: product, category, amount, date.
2. Run a query with ROW_NUMBER to rank products per category. Change the ORDER BY and observe the results.
3. Try the deduplication pattern on a real table (backup first!).
4. Read the official PostgreSQL documentation on window functions: Tutorial on Window Functions.
5. If you’re working on a complex project, check out our main guide on Advanced PostgreSQL for deeper performance and indexing topics.
6. Remember: a window function is not magic — it’s a function that runs over a set of rows. The larger the window, the heavier the query. Use indexes on ORDER BY and PARTITION BY columns to keep everything fast.
We at Meteora Web have seen report queries drop from 8 seconds to 80 milliseconds thanks to well‑written window functions. Now it’s your turn.
