SQL - Structured Query Language Advanced Window Functions Questions and Answers 1 — 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?
- LEAD(TotalSales) OVER (PARTITION BY Product ORDER BY SaleMonth)
- LAG(TotalSales) OVER (PARTITION BY Product ORDER BY SaleMonth) (Correct answer)
- FIRST_VALUE(TotalSales) OVER (PARTITION BY Product ORDER BY SaleMonth)
- ROW_NUMBER() OVER (PARTITION BY Product ORDER BY SaleMonth)
Correct answer: LAG(TotalSales) OVER (PARTITION BY Product ORDER BY SaleMonth)
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?
- RANK()
- CUME_DIST()
- NTILE(4) (Correct answer)
- PERCENT_RANK()
Correct answer: NTILE(4)
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?
- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
- RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW (Correct answer)
- ROWS UNBOUNDED PRECEDING
- RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
Correct answer: RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
`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?
- LEAD(Salary)
- NTH_VALUE(Salary, 2)
- LAST_VALUE(Salary)
- FIRST_VALUE(Salary) (Correct answer)
Correct answer: FIRST_VALUE(Salary)
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?
- PERCENT_RANK()
- CUME_DIST() (Correct answer)
- NTILE(100)
- DENSE_RANK()
Correct answer: CUME_DIST()
`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]
Question 6: Given the following query, what will be the value in the `NextSale` column for the row where `Month` is '2024-02-01'?
- 12000
- 11000 (Correct answer)
- 10000
- 0
Correct answer: 11000
The `LEAD(Sales, 1, 0)` function looks ahead one row (`offset` of 1) in the result set ordered by `Month`. For the row '2024-02-01', the next row is '2024-03-01', which has a `Sales` value of 11000. Therefore, 11000 is returned. [2, 7] The default value of 0 would only be used for the last row in the set where there is no subsequent row. [3]
SQL - Structured Query Language Advanced Window Functions Questions and Answers 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?