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?
What Practice Test Geeks adds beyond an AI chat for SQL
An AI chatbot can quiz you, but it can't reproduce the exam conditions or keep your record. Here you get:
- A timed, full-length SQL mock exam — 78 questions in 120 minutes with a 63% pass mark, modeled on the official exam's question count and time limit, followed by a score report broken down by exam domain.
- A fixed SQL question bank: the same test every attempt, so your score is comparable from one try to the next instead of changing with every regenerated quiz.
- Progress saved on your device: completed tests, scores and missed questions stay in My Progress, so you can retake only what you got wrong.
- A printable SQL cheat sheet you can print or save as a PDF.
- SQL flashcards built from the same question bank.