Window frame specification (ROWS BETWEEN) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROWS BETWEEN clause specify in a window function?

It defines the subset of rows (the frame) around the current row to include when calculating the window function.

intermediate

Explain the difference between ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.

UNBOUNDED PRECEDING AND CURRENT ROW includes all rows from the start of the partition up to the current row.<br>1 PRECEDING AND 1 FOLLOWING includes only the row before, the current row, and the row after.

intermediate

What happens if you omit the ROWS BETWEEN clause in a window function?

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which may behave differently especially with duplicate values.

beginner

Can ROWS BETWEEN frame include future rows relative to the current row?

Yes, by using FOLLOWING you can include rows after the current row in the frame.

beginner

Write a simple SQL query using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to calculate a moving sum.

SELECT date, sales, SUM(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum FROM sales_table;

What does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?

A All rows from the start of the partition up to the current row

B Only the current row

C Only the rows after the current row

D Only the previous row

This frame includes all rows from the beginning of the partition until the current row.

Which keyword specifies rows after the current row in a window frame?

A PRECEDING

B UNBOUNDED

C FOLLOWING

D CURRENT

FOLLOWING means rows after the current row.

If you want to include only the current row and the one before it, which frame would you use?

A ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING

B ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

C ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

D ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This frame includes the row before and the current row.

What is the default frame if ROWS BETWEEN is not specified?

A RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

C ROWS BETWEEN CURRENT ROW AND CURRENT ROW

D RANGE BETWEEN CURRENT ROW AND CURRENT ROW

The default frame uses RANGE mode from the start of the partition to the current row.

Which of these is a valid frame clause?

A ROWS BETWEEN 3 FOLLOWING AND 1 PRECEDING

B ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW

C ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING

D ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING

Frames must specify a start before the end; 1 PRECEDING to 2 FOLLOWING is valid.

Describe how the ROWS BETWEEN clause controls which rows are included in a window function calculation.

Think about how you select a group of rows around the current row.

Explain the difference between ROWS and RANGE in window frame specifications.

Consider how duplicates and ordering affect the frame.