SQL Advanced Window Functions 2

Question 1

Which window function assigns the same rank to ties but leaves gaps in the sequence afterward?

Accepted Answer

RANK()

Answer

RANK() gives tied rows the same rank and skips the next ranks, leaving gaps.

Question 2

What does NTILE(4) do when applied over an ordered partition?

Accepted Answer

Divides rows into 4 roughly equal buckets

Answer

NTILE(n) distributes ordered rows into n approximately equal groups.

Question 3

In LAG(salary, 2) OVER (ORDER BY hire_date), which row's value is returned?

Accepted Answer

The row 2 positions before

Answer

LAG with offset 2 accesses the value from two rows prior in the ordering.

Question 4

Which clause must accompany RANK() for deterministic results?

Accepted Answer

ORDER BY inside OVER

Answer

Ranking functions require an ORDER BY in the OVER clause to define the sequence.

Question 5

What is returned by FIRST_VALUE(price) OVER (PARTITION BY category ORDER BY price)?

Accepted Answer

The lowest price in each category

Answer

FIRST_VALUE returns the first row's value per the ordering, here the lowest price per category.

SQL Practice Test