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

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

Answer

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

Answer

ROW_NUMBER() OVER (PARTITION BY Product ORDER BY SaleMonth)

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

RANK()

Answer

CUME_DIST()

Answer

PERCENT_RANK()

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

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

Answer

ROWS UNBOUNDED PRECEDING

Answer

RANGE BETWEEN 6 PRECEDING AND CURRENT ROW

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

LEAD(Salary)

Answer

NTH_VALUE(Salary, 2)

Answer

LAST_VALUE(Salary)

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

PERCENT_RANK()

Answer

NTILE(100)

Answer

DENSE_RANK()

Question 6

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

Accepted Answer

11000

Answer

12000

Answer

10000

Answer

0