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

DENSE_RANK()

Answer

ROW_NUMBER()

Answer

NTILE()

Question 2

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

Accepted Answer

Divides rows into 4 roughly equal buckets

Answer

Returns the 4th row only

Answer

Adds 4 to each row number

Answer

Returns the top 4 rows

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

The current row

Answer

The row 2 positions ahead

Answer

The first row of the partition

Question 4

Which clause must accompany RANK() for deterministic results?

Accepted Answer

ORDER BY inside OVER

Answer

GROUP BY

Answer

HAVING

Answer

DISTINCT

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

The highest price overall

Answer

The average price

Answer

A random price

Question 6

Which function would you use to compute a running total of sales by date?

Accepted Answer

SUM() OVER (ORDER BY date)

Answer

COUNT() with GROUP BY

Answer

MAX() OVER ()

Answer

ROW_NUMBER()

Question 7

What does an empty OVER () clause cause an aggregate like AVG() to do?

Accepted Answer

Compute over the entire result set as one window

Answer

Error out

Answer

Compute per row only

Answer

Ignore NULLs differently