Window frame (ROWS BETWEEN, RANGE BETWEEN) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

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

It defines the frame of rows relative to the current row by counting physical rows before and after it.

intermediate

How does RANGE BETWEEN differ from ROWS BETWEEN in window frames?

RANGE BETWEEN defines the frame based on logical values of the ORDER BY column, including all rows with the same value, while ROWS BETWEEN counts physical rows.

beginner

What is the default window frame if none is specified?

The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning from the first row with the same ORDER BY value up to the current row.

beginner

Explain the effect of ROWS BETWEEN 2 PRECEDING AND CURRENT ROW in a window function.

It includes the current row and the two rows before it in the frame for calculation.

intermediate

Why might RANGE BETWEEN cause unexpected results with non-unique ORDER BY values?

Because it includes all rows with the same ORDER BY value, the frame can be larger than expected, affecting aggregates.

What does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?

A From two rows before to two rows after the current row

B From the current row to the last row in the partition

C Only the current row

D From the first row in the partition to the current row

This frame includes all rows from the start of the partition up to the current row.

Which window frame clause counts physical rows relative to the current row?

A RANGE BETWEEN

B ROWS BETWEEN

C GROUPS BETWEEN

D PARTITION BY

ROWS BETWEEN counts physical rows before and after the current row.

If ORDER BY column has duplicates, which frame clause includes all rows with the same value?

A RANGE BETWEEN

B PARTITION BY

C ROWS BETWEEN

D ORDER BY

RANGE BETWEEN includes all rows with the same ORDER BY value in the frame.

What is the default frame when using window functions with ORDER BY but no frame clause?

A RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B No frame is applied

C ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Which clause would you use to include exactly 3 rows before and the current row in a window frame?

A RANGE BETWEEN 3 PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING

C ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

D RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW includes the current row and 3 rows before it.

Describe the difference between ROWS BETWEEN and RANGE BETWEEN in window frames.

Think about counting rows versus counting values.

Explain why the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and what it means for aggregate calculations.

Consider how cumulative sums or averages work.