Skip to main content
Practice

1

deck​

1. What Are Window Functions?​

Window functions perform calculations across related rows — without collapsing them.

They let you keep all the rows while adding insights like rankings, running totals, or row comparisons.


2. Why Use Them?​

You can use window functions to:

  • Rank rows within a group
  • Compute running totals or moving averages
  • Compare each row to others in the same dataset
  • Add insights without losing row-level detail

===

3. Basic Syntax​

The basic syntax for a window function is as follows:

SELECT name, grade,
  RANK() OVER (PARTITION BY class ORDER BY grade DESC) AS rank
FROM students;
  • OVER() defines the window
  • You can partition and/or order the rows
  • The function runs across the window, not the full table

===

4. Components of OVER()​

The OVER() clause is used to define the window.

It can include:

  • PARTITION BY — to restart the calculation for each group (like a department or course)
  • ORDER BY — to define sequence for calculations like rankings or running totals

Both are optional, but using them smartly is key to powerful analysis.


===

5. Window vs Aggregate Functions​

FeatureAggregate FunctionsWindow Functions
Collapse rows✅❌
Keep all original rows❌✅
Add insights per row❌✅

Window functions enhance each row, while aggregates summarize groups.


===

6. Common Use Cases​

  • RANK(), DENSE_RANK(), ROW_NUMBER() for rankings
  • SUM(), AVG() with OVER() for per-row aggregates
  • LEAD(), LAG() for comparing current row with others

Window functions help answer how this row compares — not just what's the total.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.