Expert

Window Frame Specification (ROWS vs RANGE)

SQL Mastery Team
March 29, 2026
6 min read

It's **Day 67**, and we're getting technical. Yesterday we used `ROWS`. Today, we learn about its cousin, `RANGE`.

The Theory

  • **ROWS**: Counts the physical rows in the result set. (`3 PRECEDING` means exactly 3 rows up).
  • **RANGE**: Looks at the **Values** in the columns. (`RANGE BETWEEN INTERVAL '3 days' PRECEDING` looks at the last 3 days of time, regardless of how many rows there are).

    • Why RANGE is safer for Dates

    Imagine your store was closed on Tuesday.

  • **ROWS BETWEEN 2 PRECEDING**: Will pull data from Monday and the previous Sunday.
  • **RANGE BETWEEN '2 days' PRECEDING**: Realizes Tuesday is missing and only pulls from Monday (if within the 2-day window).

    • The Code Example

    -- This handles missing days correctly in Postgres

    AVG(revenue) OVER(

    ORDER BY sale_date

    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

    )

    When to use ROWS

    Use `ROWS` when the order is strictly numerical (like ID) or when you specifically want a fixed sample size (like the "last 100 orders").

    Your Task for Today

    Write a query using `ROWS` and then the same query using `RANGE` and look at the difference when there are "Gaps" in your data.

    *Day 68: PERCENT_RANK & NTILE—Segmenting your data.*

    Ready to put your knowledge into practice?

    Join SQL Mastery and learn through interactive exercises.