SQL - Structured Query Language Advanced Window Functions Questions and Answers

Question 1

A financial analyst needs to calculate the month-over-month sales growth. The table `MonthlySales` has columns `Product`, `SaleMonth`, and `TotalSales`. Which window function is best suited to retrieve the previous month's sales amount on the same row as the current month's sales to facilitate the calculation?

Accepted Answer

LAG(TotalSales) OVER (PARTITION BY Product ORDER BY SaleMonth)

Answer

The `LAG()` function is designed to access data from a previous row within the same result set without the use of a self-join. [18] By partitioning by `Product` and ordering by `SaleMonth`, `LAG(TotalSales)` will retrieve the `TotalSales` value from the preceding month for the same product, which is exactly what is needed to calculate month-over-month growth. [17, 14]

Question 2

A marketing team wants to segment its customer base into four equal-sized groups (quartiles) based on their total purchase amount to identify top spenders. Which window function is specifically designed to divide an ordered partition of rows into a specified number of ranked groups?

Accepted Answer

NTILE(4)

Answer

The `NTILE(n)` function is the correct choice as it distributes the rows in an ordered partition into a specified number of groups, in this case, 4. [1, 8] It assigns a rank from 1 to `n` for each group, which is ideal for creating quartiles, deciles, or other percentile-based segments. [13]

Question 3

When calculating a 7-day moving average for website traffic, the data table `TrafficLog` has multiple entries for the same date. To ensure the window frame for the average calculation correctly includes all data from the 6 preceding calendar days plus the current day (regardless of the number of rows), which framing clause is most appropriate?

Accepted Answer

RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW

Answer

`RANGE` defines the window frame based on the values in the `ORDER BY` column, whereas `ROWS` defines it by the physical position of rows. [21] When dealing with dates that might have duplicates or gaps, `RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW` correctly creates a logical window of seven calendar days. `ROWS BETWEEN 6 PRECEDING` would only look at the 6 previous rows, which is incorrect if a day has multiple entries. `RANGE BETWEEN 6 PRECEDING` is not valid for date intervals; it requires the `INTERVAL` keyword. [27]

Question 4

A data analyst wants to create a report showing each employee's salary alongside the highest salary within their respective department. Which of the following window functions, when used with `OVER (PARTITION BY Department ORDER BY Salary DESC)`, will correctly identify the top salary for the department on every employee's row?

Accepted Answer

FIRST_VALUE(Salary)

Answer

The `FIRST_VALUE()` function returns the value of the specified expression from the first row of the window frame. [28] By partitioning by `Department` and ordering by `Salary DESC` (descending), the first row in each partition will always be the one with the highest salary. `FIRST_VALUE(Salary)` will therefore return this maximum salary for every row within that department's partition. [24, 26]

Question 5

A university needs to find the relative rank of each student's GPA, defined as the percentage of students with a GPA less than or equal to the current student's GPA. Which window function calculates this cumulative distribution?

Accepted Answer

CUME_DIST()

Answer

`CUME_DIST()` calculates the cumulative distribution of a value within a group of values. Specifically, it computes the fraction of partition rows that are less than or equal to the current row's value, which matches the requirement perfectly. [5, 15] `PERCENT_RANK()` calculates a different metric: `(rank - 1) / (total_rows - 1)`. [9]

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language Advanced Window Functions Questions and Answers