Course contentsShow
← Data Science
Lesson 1020 of 2,14526. Advanced SQLFree lesson

UNBOUNDED and CURRENT ROW Keywords

Master frame boundary specifications using UNBOUNDED PRECEDING, CURRENT ROW, and UNBOUNDED FOLLOWING.

UNBOUNDED and CURRENT ROW Keywords

What You'll Learn

You'll master the precise frame boundary keywords that control exactly which rows your window functions analyze.

Understanding Frame Boundaries

When working with window frames, you need to tell SQL exactly where the frame starts and ends. Three keywords give you this control:

  • UNBOUNDED PRECEDING: Start at the very first row of the partition
  • CURRENT ROW: Start or end at the current row being processed
  • UNBOUNDED FOLLOWING: End at the very last row of the partition

Think of it like reading a book: UNBOUNDED PRECEDING means "from the cover," CURRENT ROW means "this page I'm on," and UNBOUNDED FOLLOWING means "to the back cover."

How They Combine

These keywords work in pairs to define frames:

-- From partition start to current row (cumulative sum)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Only the current row
ROWS BETWEEN CURRENT ROW AND CURRENT ROW

-- Current row and all following rows
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- Entire partition (default for most aggregates)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Practical Example

SELECT 
    order_date,
    sales,
    SUM(sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_day_avg
FROM orders;

The first window creates a running total from the start, while the second creates a moving average of just the last 3 days.

Key Takeaway: UNBOUNDED PRECEDING/FOLLOWING and CURRENT ROW let you precisely control which rows your window function includes, enabling everything from cumulative calculations to fixed-size moving windows.