SQL Advanced Window Functions 3

Question 1

What is the default window frame when ORDER BY is specified but no frame clause is given?

Accepted Answer

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Answer

The SQL default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Question 2

How do ROWS and RANGE frame modes differ when there are duplicate ORDER BY values?

Accepted Answer

ROWS counts physical rows; RANGE groups peers with equal values

Answer

ROWS treats each row individually while RANGE includes all peer rows sharing the ordering value.

Question 3

What does LEAD(value, 1, 0) return when no following row exists?

Accepted Answer

0

Answer

The third argument supplies a default (0 here) when the offset row does not exist.

Question 4

Which window function returns a value between 0 and 1 representing relative standing?

Accepted Answer

PERCENT_RANK()

Answer

PERCENT_RANK() returns a relative rank as a fraction from 0 to 1.

Question 5

In ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, how many rows are in the frame at most?

Accepted Answer

3

Answer

The frame spans the previous, current, and next rows for a maximum of three.

SQL Practice Test