Why this chapter matters
Windows provide advanced analysis without collapsing detail rows, which is critical for trend and cohort work.
What you will learn
- Use PARTITION BY and ORDER BY inside OVER clauses correctly.
- Compute rankings, running sums, and lag or lead comparisons.
- Choose window frames that match analytical intent.
Understand the core ideas
Window functions in PostgreSQL let you compute metrics across related rows while keeping each original row visible. This is different from GROUP BY, which collapses rows into summaries. The power comes from defining the analytical neighborhood with OVER (...). PARTITION BY sets independent groups, ORDER BY sets sequence inside each group, and an optional frame sets how many rows are included for each calculation. If you skip explicit framing for cumulative metrics, defaults may not match your intent, so be deliberate. For running totals, many analysts use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to make scope explicit. For ranking, choose among ROW_NUMBER, RANK, and DENSE_RANK based on tie behavior. Correct window use starts with grain clarity: if each row is one product-month, your window logic should never accidentally cross product boundaries unless that is the goal.
A common workflow is to create a monthly revenue table first, then apply windows to that stable grain. This avoids noisy behavior that appears when raw transaction rows are used directly for rankings or running sums. LAG and LEAD support change analysis by exposing previous or next row values in the defined order, which helps compute deltas and growth rates. Always specify deterministic ordering columns because duplicate timestamps or equal revenue values can otherwise produce unstable comparisons. In PostgreSQL, window functions are computed after FROM, WHERE, and grouping steps, so structure your query to produce the intended base rows first. Validate outputs by manually checking a small partition, such as one category across three months, to ensure rank and running totals match expectations. Window functions are precise tools when partition, order, and frame are all intentionally defined.
Key terms
- partition
- A subset of rows over which a window function is calculated independently.
- window frame
- The row range within a partition used for each window calculation.
- running total
- A cumulative sum that adds current and prior rows in a defined order.
- lag
- A window function that returns a value from a prior row in the same partition.
Category ranking with cumulative monthly revenue
Use monthly_product_revenue(month_start, category_id, product_id, revenue) with one row per product per month. Need ranking and cumulative category revenue within each month.
- Partition by
month_start, category_idfor rank so each product is compared only against peers in the same month-category slice. - Order rank by
revenue DESC, product_id ASCto keep tie handling deterministic across reruns. - Compute
DENSE_RANK()for leaderboard position and keep all product rows intact. - Compute cumulative revenue with
SUM(revenue) OVER (PARTITION BY month_start, category_id ORDER BY revenue DESC, product_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
SELECT month_start, category_id, product_id, revenue, DENSE_RANK() OVER (PARTITION BY month_start, category_id ORDER BY revenue DESC, product_id ASC) AS revenue_rank, SUM(revenue) OVER (PARTITION BY month_start, category_id ORDER BY revenue DESC, product_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue FROM monthly_product_revenue ORDER BY month_start, category_id, revenue_rank, product_id; returns one row per product-month with rank and cumulative context.A common misconception
Claim: Window functions always reduce data to one row per group.
Correction: Window functions preserve row count. They add calculated columns to each row, unlike GROUP BY which collapses rows.
Lessons in this chapter
- Window function anatomyUnderstand function, partition, order, and frame pieces in PostgreSQL window syntax. Read the full guide →
- Ranking patternsUse ROW_NUMBER, RANK, and DENSE_RANK for leaderboard and top-N analysis.
- Running and moving metricsBuild cumulative and rolling calculations with explicit frame definitions.
- Comparative row analysisUse LAG and LEAD to measure change between adjacent events.
Study task
Chapter checkpoint
Which PostgreSQL clause inside OVER splits rows into independent groups for window calculations?
PARTITION BY splits rows into independent groups for each window calculation.