SQL Advanced Window Functions 5

Question 1

To compute a 3-day moving average of revenue, which frame is appropriate?

Accepted Answer

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Answer

Averaging the current and two prior rows gives a trailing 3-row moving average.

Question 2

What is the difference between RANK() and DENSE_RANK() output for values 10,10,20?

Accepted Answer

RANK gives 1,1,3 and DENSE_RANK gives 1,1,2

Answer

RANK skips to 3 after a tie while DENSE_RANK continues to 2 with no gap.

Question 3

Which is true about PARTITION BY in a window function?

Accepted Answer

It divides rows into independent groups for the calculation

Answer

PARTITION BY splits rows into groups within which the window function operates independently.

Question 4

What does SUM(amount) OVER (PARTITION BY user ORDER BY ts ROWS UNBOUNDED PRECEDING) produce?

Accepted Answer

A running cumulative sum per user

Answer

Ordering with UNBOUNDED PRECEDING to current row yields a per-user running total.

Question 5

Why can chaining window functions sometimes require a CTE?

Accepted Answer

Window functions cannot be nested directly in one another

Answer

You cannot nest a window function inside another, so a CTE materializes the first result for the second.

SQL Practice Test