4 Advanced SQL ROWS Window Function Interview Questions with Practical Examples and Step-by-Step Solutions

Learn how to use the advanced SQL ROWS window function with practical examples.

Example 1: Running Total

Calculates a cumulative (running) total of employee salaries.

SELECT
    EmployeeID,
    Salary,
    SUM(Salary) OVER (
        ORDER BY EmployeeID
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS TotalSalary
FROM Employees;

Output

Output

Explanation:

Iteration 1:

Current row: EmployeeID = 1

· SQL starts from the first row because of UNBOUNDED PRECEDING.

· There are no previous rows.

· SQL includes only the current row.

Calculation:

1000 = 1000

Result:

TotalSalary = 1000

Iteration 2:

Current row: EmployeeID = 2

· SQL starts from the first employee.

· It includes the previous row (EmployeeID = 1) and the current row (EmployeeID = 2).

Calculation:

1000 + 2000 = 3000

Result:

TotalSalary = 3000

Iteration 3:

Current row: EmployeeID = 3

· SQL starts from the first employee.

· It includes EmployeeID 1, 2, and the current row (EmployeeID = 3).

Calculation:

1000 + 2000 + 3000 = 6000

Result:

TotalSalary = 6000

Iteration 4:

Current row: EmployeeID = 4

· SQL starts from the first employee.

· It includes EmployeeID 1, 2, 3, and the current row (EmployeeID = 4).

Calculation:

1000 + 2000 + 3000 + 4000 = 10000

Result:

TotalSalary = 10000

Iteration 5:

· SQL checks for the next current row.

· No more rows are available.

· The calculation stops.

Final Understanding

At every step, the current row moves forward, and the window frame expands because UNBOUNDED PRECEDING always starts from the first row.

That is why the total keeps increasing:

1000 → 3000 → 6000 → 10000

Example 2: 7-Day Moving Average

Calculates the average sales for the current day and the previous 6 days (7 days total).

SELECT SalesDate,
    SalesAmount,
    AVG(SalesAmount) OVER (
        ORDER BY SalesDate
        ROWS BETWEEN 6 PRECEDING
        AND CURRENT ROW
    ) AS SevenDayAverage
FROM DailySales;

Output

Understanding the Window Frame

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means:

• Start from 6 rows before the current row
• Include the current row
• Calculate the average using those rows

Maximum rows included in the window frame:

6 previous rows + current row = 7 rows

Iteration 1:

Current Row: 2026–01–01

• SQL looks for 6 previous rows.
• No previous rows exist.
• SQL includes only the current row.

Calculation:

AVG(100) = 100

Result:

SevenDayAverage = 100

Iteration 2:

Current Row: 2026–01–02

SQL includes:

• 2026–01–01 (previous row)
• 2026–01–02 (current row)

Calculation:

(100 + 120) / 2 = 110

Result:

SevenDayAverage = 110

Iteration 3:

Current Row: 2026–01–03

SQL includes:

• 2026–01–01
• 2026–01–02
• 2026–01–03 (current row)

Calculation:

(100 + 120 + 150) / 3 = 123.33

Result:

SevenDayAverage = 123.33

Iteration 4:

Current Row: 2026–01–04

SQL includes:

• 2026–01–01
• 2026–01–02
• 2026–01–03
• 2026–01–04 (current row)

Calculation:

(100 + 120 + 150 + 180) / 4 = 137.50

Result:

SevenDayAverage = 137.50

Iteration 5:

Current Row: 2026–01–05

SQL includes:

• 2026–01–01
• 2026–01–02
• 2026–01–03
• 2026–01–04
• 2026–01–05 (current row)

Calculation:

(100 + 120 + 150 + 180 + 170) / 5 = 144

Result:

SevenDayAverage = 144

Iteration 6:

Current Row: 2026–01–06

SQL includes:

• 2026–01–01
• 2026–01–02
• 2026–01–03
• 2026–01–04
• 2026–01–05
• 2026–01–06 (current row)

Calculation:

(100 + 120 + 150 + 180 + 170 + 200) / 6
= 153.33

Result:

SevenDayAverage = 153.33

Iteration 7:

Current Row: 2026–01–07

SQL now has 6 previous rows available.

Window frame includes:

• 2026–01–01
• 2026–01–02
• 2026–01–03
• 2026–01–04
• 2026–01–05
• 2026–01–06
• 2026–01–07 (current row)

Calculation:

(100 + 120 + 150 + 180 + 170 + 200 + 220) / 7
= 162.86

Result:

SevenDayAverage = 162.86

Iteration 8:

Current Row: 2026–01–08

Now the window frame can contain only 7 rows.

SQL removes the oldest row (2026–01–01) and moves forward.

Window frame:

• 2026–01–02
• 2026–01–03
• 2026–01–04
• 2026–01–05
• 2026–01–06
• 2026–01–07
• 2026–01–08 (current row)

Calculation:

(120 + 150 + 180 + 170 + 200 + 220 + 210) / 7
= 178.57

Result:

SevenDayAverage = 178.57

Final Output

In the first 6 rows, SQL uses all available previous rows. After the 7th row, the window frame becomes a fixed size of 7 rows and starts sliding forward. This is why it is called a 7-day moving average.

Example 3: Rolling Sum

Calculates the sum of revenue for the current month and the previous two months (3-month rolling total).

SELECT Month,
    Revenue,
    SUM(Revenue) OVER (
        ORDER BY Month
        ROWS BETWEEN 2 PRECEDING
        AND CURRENT ROW
    ) AS RollingRevenue
FROM MonthlyRevenue;

Let’s explain this query step-by-step with 5 iterations.

Understanding the Window Frame

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

means:

• Include 2 rows before the current row
• Include the current row
• Total window frame size = 3 rows (2 previous rows + current row)

Iteration 1:

Current Row: Jan

• SQL looks for 2 previous rows.
• No previous rows are available.
• SQL includes only the current row.

Month Revenue: Jan = 10000 (Current Row)

Calculation:

10000 = 10000

Result:

RollingRevenue = 10000

Iteration 2:

Current Row: Feb

• SQL includes the previous row and the current row. As per frame required 2 previous row + current row but at this stage found only 1 pervious row Jan.

Month Revenue : Jan = 10000 , Feb = 15000 (Current Row)

Calculation:

10000 + 15000 = 25000

Result:

RollingRevenue = 25000

Iteration 3:

Current Row: Mar

• SQL includes 2 previous rows and the current row.

Month Revenue : Jan = 10000, Feb = 15000, Mar = 12000 (Current Row)

Calculation:

10000 + 15000 + 12000 = 37000

Result:

RollingRevenue = 37000

Iteration 4:

Current Row: Apr

• SQL moves to the next row.
• It includes the previous 2 months and the current month.

Window Frame:

Month Revenue: Feb = 15000, Mar = 12000, Apr = 18000 (Current Row)

Calculation:

Sum(15000 + 12000 + 18000 = 45000

Result:

RollingRevenue = 45000

Notice that Jan is removed because the window frame only keeps 2 previous rows + current row.

Iteration 5:

Current Row: May

Window Frame:

Month Revenue: Mar = 12000, Apr = 18000, May = 20000 (Current Row)

Calculation:

12000 + 18000 + 20000 = 50000

Result:

RollingRevenue = 50000

Key Understanding

At the beginning, SQL uses all available rows because there are not enough previous rows. Once SQL reaches the 3rd row, the window frame reaches its maximum size:

2 Previous Rows + Current Row = 3 Rows

After that, the window frame starts sliding forward:

• The oldest row leaves the frame.
• The new current row enters the frame.

This is why it is called a 3-month rolling sum.

Example 4: Sum of the Previous Two Rows

Calculates the total price of only the previous two products. The current product is not included.

SELECT ProductID,
    Price,
    SUM(Price) OVER (
        ORDER BY ProductID
        ROWS BETWEEN 2 PRECEDING
        AND 1 PRECEDING
    ) AS PreviousTwoTotal
FROM Products;

In this example, the current row is not included in the calculation. SQL sums only the two rows immediately before the current row.

Output

Let’s explain this query step-by-step with 5 iterations.

Understanding the Window Frame

ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING

means:

• Start from 2 rows before the current row
• End at 1 row before the current row
• Do not include the current row

So SQL only calculates the sum of the previous two rows.

Iteration 1:

Current Row: ProductID = 1

• SQL looks for the previous 2 rows.
• No previous rows exist.
• The window frame is empty.

Calculation:

No rows available

Result:

PreviousTwoTotal = NULL

Iteration 2:

Current Row: ProductID = 2

• SQL looks at rows before the current row.
• Only ProductID = 1 is available.

Window Frame:

ProductID 1 = Price1500

Calculation:

500 = 500

Result:

PreviousTwoTotal = 500

Iteration 3:

Current Row: ProductID = 3

• SQL looks at the previous 2 rows.
• It includes ProductID = 1 and ProductID = 2.
• Current row (ProductID = 3) is excluded.

Window Frame:

ProductID 1 Price = 500, ProductID 2 Price = 2700

Calculation:

500 + 700 = 1200

Result:

PreviousTwoTotal = 1200

Iteration 4:

Current Row: ProductID = 4

• SQL moves forward.
• It includes the two rows before ProductID = 4.

Window Frame:

ProductID 2 Price = 700, ProductID 3 Price = 600

Calculation:

700 + 600 = 1300

Result:

PreviousTwoTotal = 1300

Notice that ProductID = 1 is removed because SQL only keeps the previous two rows.

Iteration 5:

Current Row: ProductID = 5

• SQL looks at the previous 2 rows.
• It includes ProductID = 3 and ProductID = 4.
• Current row (ProductID = 5) is excluded.

Window Frame:

ProductID 3 Price = 600, ProductID 4 Price = 4900

Calculation:

600 + 900 = 1500

Result:

PreviousTwoTotal = 1500

Key Understanding

Unlike:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

which includes the current row:

Previous rows + Current row

this frame:

ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING

only looks backward:

Previous 2 rows only (Current row excluded)

This is useful when you need to compare the current row with previous values, such as calculating previous period totals, changes, or trends.