LearnLive

SQL Analysis · Chapter 6 of 10

Window Functions

Apply PostgreSQL window functions to rank rows, compute running totals, and compare each row to group context.

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.

Lessons in this chapter

  1. Window function anatomyUnderstand function, partition, order, and frame pieces in PostgreSQL window syntax. Read the full guide →
  2. Ranking patternsUse ROW_NUMBER, RANK, and DENSE_RANK for leaderboard and top-N analysis.
  3. Running and moving metricsBuild cumulative and rolling calculations with explicit frame definitions.
  4. Comparative row analysisUse LAG and LEAD to measure change between adjacent events.

Study task

In PostgreSQL, rank products by monthly revenue within each category and include a running revenue total per category.

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.