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

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Answer

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Answer

The whole partition

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

They are identical

Answer

RANGE counts physical rows

Answer

ROWS ignores ties entirely

Question 3

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

Accepted Answer

0

Answer

NULL

Answer

The current value

Answer

An error

Question 4

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

Accepted Answer

PERCENT_RANK()

Answer

ROW_NUMBER()

Answer

RANK()

Answer

NTILE()

Question 5

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

Accepted Answer

3

Answer

1

Answer

2

Answer

Unbounded

Question 6

Why might you use a window function instead of GROUP BY for aggregates?

Accepted Answer

To keep individual rows while adding aggregate columns

Answer

To collapse rows into one

Answer

To avoid ORDER BY

Answer

To improve indexing

Question 7

What does CUME_DIST() compute?

Accepted Answer

The cumulative distribution: fraction of rows at or below the current value

Answer

The cumulative count of rows

Answer

The distance between partitions

Answer

The number of distinct values