Advanced Window Functions Flashcards

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?

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]

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?

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]

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?

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]

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?

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]

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?

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]

Given the following query, what will be the value in the `NextSale` column for the row where `Month` is '2024-02-01'?

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]