🧠 How Window Functions Work: The Fundamentals

🚀 What Are Window Functions, Really?

Window functions compute values across a set of rows that are somehow related to the current row, without collapsing the result set. They help you compare, rank, or accumulate across rows while preserving individual detail.

Unlike aggregate functions (which group and collapse data), window functions add extra insight per row.

🔧 Syntax Snapshot: Core Anatomy

FUNCTION(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  [ROWS or RANGE BETWEEN ...]
)

Each clause plays a specific role:

• PARTITION BY → divides the dataset into groups
• ORDER BY → defines how rows are sequenced within those groups
• ROWS BETWEEN → defines which rows to include in each calculation

🔍 PARTITION BY Demystified

Think of PARTITION BY as creating mini subgroups within your data. Each group resets the window function logic.

🧾 Sample Data

🔄 Comparison: With vs Without PARTITION BY

SELECT CustomerID, OrderDate, Amount,
       ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum_All,
       ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum_ByCustomer
FROM Orders;

📌 Notice how PARTITION BY resets the row numbering inside each group.

• PARTITION BY CustomerID restarts the function for each customer
• Without it, the window applies across all rows

📌 Use PARTITION BY when you want results relative to a subgroup (e.g., a customer’s order rank).

📐 ORDER BY Inside the Window

ORDER BY determines the sequence the function uses inside each partition.

• Example: ORDER BY OrderDate means "oldest to newest"
• Omitting it? You may get unpredictable or default behavior

Changing the ORDER changes the meaning. It defines what “first”, “last”, or “next” really means.

🪟 Understanding Frame Clauses

A frame clause controls which rows are visible to the window function for each row.

Common Frame Patterns:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW → running total
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING → forward-looking window
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → moving average (3-row)

🧾 Sample Data

🔎 Query Example

SELECT OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
       SUM(Amount) OVER (ORDER BY OrderDate
                         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FutureTotal,
       AVG(Amount) OVER (ORDER BY OrderDate
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3
FROM Orders;

📊 Result

ROWS BETWEEN vs RANGE BETWEEN

• ROWS = includes a specific number of rows relative to the current row
• RANGE = includes rows based on values being equal to the current row’s ORDER BY value

Example:

If OrderDate has duplicates:

Using ROWS BETWEEN CURRENT ROW AND CURRENT ROW only includes one row. Using RANGE BETWEEN CURRENT ROW AND CURRENT ROW includes both rows with 2023-01-01.

🔍 Use RANGE when you want to include all rows with matching ORDER BY values.

📌 ROWS is more precise for counting.
📌 RANGE is better for grouped value comparisons.

Default behavior varies by function:

• Ranking functions often don’t need explicit frames
• LAST_VALUE() usually does

📦 Why Use CTEs with Window Functions?

A Common Table Expression (CTE) is often necessary when you want to filter or manipulate the results of a window function.

Why?

You can't directly reference a window function result in the WHERE clause or reuse it in the same SELECT — SQL doesn't allow that. But by putting it in a CTE, you can treat the computed column as a normal field.

🧾 Example

Find customers with the top 10% order amounts:

WITH Ranked AS (
  SELECT *,
         PERCENT_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS pr
  FROM Orders
)
SELECT * FROM Ranked WHERE pr <= 0.10;

📌 The window function runs inside the CTE, and the outer query can safely filter on it.

⚠️ Common Mistakes and How to Spot Them

• ❌ Forgetting ORDER BY → RANK() will return 1 for all rows. Without ordering, many functions treat all rows as ties.
• ❌ Applying filters in WHERE after window logic → may exclude needed rows. You must use CTEs or subqueries to filter after computing window results.
• ❌ Expecting default behavior with frames → especially LAST_VALUE() surprises. If you don’t specify a frame, you might get unexpected results like the current row instead of the actual last row.
• ❌ Using aggregates when a window function was needed (collapses rows!) → Aggregates like SUM() without OVER() eliminate row detail; window versions keep all rows.

💡 Tips for Learning and Debugging

• Use ROW_NUMBER() to test sorting logic
• Always specify ORDER BY, even when optional
• Try SELECT * alongside your window to verify inputs
• Start small (2-5 rows) to visually trace the function