Window Frames and Advanced Patterns

Fine-Tuning Which Rows Get Included

So far, we’ve let SQL use default behavior for running calculations. But what if you need exactly the last 3 rows? Or a centered moving average? Or everything except the current row? Window frames give you that control.

This is also where interviewers test whether you really understand window functions or just memorized some patterns.

The Default Frame (This Trips People Up)

When you write:

SUM(amount) OVER (ORDER BY date)

SQL actually interprets it as:

SUM(amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

That means: “sum all rows from the start up to the current row.” Which is why you get a running total.

Window Frame Syntax

The full syntax:

function() OVER (
    [PARTITION BY columns]
    [ORDER BY columns]
    [ROWS | RANGE] BETWEEN start AND end
)

Start and end can be:

• UNBOUNDED PRECEDING: the first row of the partition
• n PRECEDING: n rows before current
• CURRENT ROW: this row
• n FOLLOWING: n rows after current
• UNBOUNDED FOLLOWING: the last row of the partition

ROWS vs RANGE

ROWS counts physical rows. RANGE includes all rows with the same ORDER BY value.

In practice, ROWS is clearer and more predictable. Use RANGE when you specifically need to include ties.

-- ROWS: exactly 2 rows before current
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- RANGE: all rows with values within 2 of current
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

If you have dates with multiple rows per date, RANGE may return more rows than you expect. ROWS is safer unless you have a specific reason.

Common Frame Patterns

Running Total (All Previous Rows)

SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) -- This is the default, but explicit is clearer

a Moving Average

SELECT
  invoicedate,
  description,
  unitprice,
  AVG(unitprice) OVER (
    ORDER BY invoicedate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM online_retail
WHERE quantity > 0
ORDER BY invoicedate
FETCH FIRST 20 ROWS ONLY;
SELECT
  invoicedate,
  description,
  unitprice,
  AVG(unitprice) OVER (
    ORDER BY invoicedate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM online_retail
WHERE quantity > 0
ORDER BY invoicedate
LIMIT 20;

SELECT
  invoicedate,
  description,
  unitprice,
  AVG(unitprice) OVER (
    ORDER BY invoicedate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM online_retail
WHERE quantity > 0
ORDER BY invoicedate
LIMIT 20;

Centered Moving Average

AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
) -- 5-row average centered on current

Entire Partition (No Frame)

AVG(salary) OVER (PARTITION BY department) -- No ORDER BY, no frame = entire partition

SELECT
  first_name,
  department,
  salary,
  FIRST_VALUE(first_name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS top_earner
FROM employee;

FIRST_VALUE gets the first row in the ordered window. In this case, the highest-paid person in each department.

The Consecutive Days Pattern

This one comes up in interviews a lot: “Find users with 7+ consecutive days of activity.” It’s harder than it looks.

The trick: for consecutive dates, (date - row_number) gives the same value. Non-consecutive dates give different values.

WITH numbered AS (
    SELECT
        user_id,
        login_date,
        login_date - (ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ))::int AS streak_group
    FROM user_logins
)
SELECT
    user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT() AS streak_length
FROM numbered
GROUP BY user_id, streak_group
HAVING COUNT() >= 7;

If a user logged in on Jan 1, 2, 3, then skipped Jan 4, then logged in Jan 5, 6:

• Jan 1 (row 1): Jan 1 - 1 = Dec 31 → streak_group = Dec 31
• Jan 2 (row 2): Jan 2 - 2 = Dec 31 → same streak_group!
• Jan 3 (row 3): Jan 3 - 3 = Dec 31 → same streak_group!
• Jan 5 (row 4): Jan 5 - 4 = Jan 1 → different streak_group
• Jan 6 (row 5): Jan 6 - 5 = Jan 1 → same streak_group as Jan 5

The consecutive days have the same streak_group. The break creates a new one.

Interview Pattern Cheat Sheet

• Top N per group: RANK/ROW_NUMBER + PARTITION BY, filter in outer query
• Running total: SUM() OVER (ORDER BY …)
• Period-over-period: LAG() for previous value, calculate difference
• Moving average: AVG() OVER (ROWS BETWEEN n PRECEDING AND CURRENT ROW)
• Percentile: NTILE(100) or PERCENT_RANK()
• Consecutive streaks: ROW_NUMBER() + date arithmetic

Advanced Patterns

Combine DENSE_RANK() with multiple CTEs to compare rankings across months.

Top Items per Category

Use RANK() with PARTITION BY to find top items in each category.

Consecutive Streaks

Apply the row_number + date difference technique. This is the streak pattern.

Running Total vs Moving Average

SELECT
  invoicedate,
  unitprice,
  SUM(unitprice) OVER (ORDER BY invoicedate) AS running_total,
  AVG(unitprice) OVER (
    ORDER BY invoicedate
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS moving_avg_5
FROM online_retail
WHERE quantity > 0
ORDER BY invoicedate
FETCH FIRST 25 ROWS ONLY;
SELECT
  invoicedate,
  unitprice,
  SUM(unitprice) OVER (ORDER BY invoicedate) AS running_total,
  AVG(unitprice) OVER (
    ORDER BY invoicedate
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS moving_avg_5
FROM online_retail
WHERE quantity > 0
ORDER BY invoicedate
LIMIT 25;

SELECT
  invoicedate,
  unitprice,
  SUM(unitprice) OVER (ORDER BY invoicedate) AS running_total,
  AVG(unitprice) OVER (
    ORDER BY invoicedate
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS moving_avg_5
FROM online_retail
WHERE quantity > 0
ORDER BY invoicedate
LIMIT 25;

Key Takeaways

• Window frames control exactly which rows are included
• ROWS counts physical rows; RANGE includes ties
• Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• LAST_VALUE needs explicit UNBOUNDED FOLLOWING to see whole partition
• Consecutive streak detection uses ROW_NUMBER + date arithmetic
• These patterns come up regularly in interviews and daily analytics work.

What You Can Do Now

• Filter, sort, group, and aggregate data across any table structure
• Write joins, subqueries, and CTEs to answer multi-table questions
• Manipulate dates, strings, and types across PostgreSQL, MySQL, SQL Server, and Oracle
• Build ranked leaderboards, running totals, and period-over-period comparisons
• Use window frames, LAG/LEAD, and moving averages for advanced analytics
• Detect consecutive streaks and deduplicate records with window patterns

Where to Go from Here

Practice is what turns knowledge into fluency. StrataScratch has hundreds of SQL questions from real company interviews — start with the ones tagged at your level and work up. If you haven’t already, try the Python learning path as well. Most data roles expect both, and the concepts map closely: GROUP BY is groupby, JOIN is merge, window functions are transform and rank. Knowing both makes you faster in each.

The best next step is a real project. StrataScratch Data Projects give you guided, end-to-end analyses on real datasets — pick one that interests you and put your skills to work. That’s where learning becomes craft.