Question 1

What is a window function in SQL?

Accepted Answer

A window function performs a calculation across a set of rows related to the current row, without collapsing the results into a single output row. Unlike GROUP BY which reduces the number of rows, a window function preserves all rows in the result while adding a computed column. The OVER() clause defines the window over which the calculation is performed.

Question 2

Which clause is mandatory to define a window function?

Accepted Answer

The OVER() clause is mandatory for any window function. It tells the SQL engine that the function should be executed as a window function rather than a regular aggregate function. OVER() can contain PARTITION BY, ORDER BY and a frame specification, but it can also remain empty to apply the calculation across the entire result set.

Question 3

What is the role of PARTITION BY in a window function?

Accepted Answer

PARTITION BY divides the set of rows into independent groups (partitions), and the window function is applied separately to each partition. Unlike GROUP BY, PARTITION BY does not reduce the number of rows in the result. For example, SUM(sales) OVER(PARTITION BY region) calculates total sales per region while preserving each individual row.

Question 4

What is the main difference between GROUP BY and PARTITION BY?

Accepted Answer

GROUP BY aggregates rows and returns a single row per group, thus reducing the total number of rows. PARTITION BY, used in a window function, preserves all original rows while performing a calculation on each partition. Both allow grouping data, but only GROUP BY reduces the result set.

Question 5

What does ROW_NUMBER() OVER(ORDER BY salary DESC) return?

Accepted Answer

ROW_NUMBER() assigns a unique sequential number to each row within the partition, starting at 1. With ORDER BY salary DESC, the row with the highest salary receives number 1, the next one number 2, and so on. Even if two rows have the same salary, they receive different numbers in an arbitrary manner.

Question 6

What is the difference between RANK() and DENSE_RANK() in case of ties?

Accepted Answer

RANK() assigns the same rank to tied rows then skips subsequent ranks. For example, if two rows are tied at rank 2, the next one will be rank 4. DENSE_RANK() also assigns the same rank to ties but never skips a rank: after two rows at rank 2, the next will be rank 3. This choice impacts ranking analyses, especially when identifying top N values.

Question 7

How to get the value from the previous row in a window function?

Accepted Answer

LAG() allows access to the value of a column in a previous row of the partition. By default, LAG(column) returns the value from the immediately preceding row (offset of 1). It is possible to specify a different offset and a default value if no previous row exists. This is very useful for calculating period-over-period changes, such as monthly revenue growth.

Question 8

What does LEAD(revenue, 1) OVER(ORDER BY month) return?

Accepted Answer

LEAD() accesses the value from a subsequent row in the partition. LEAD(revenue, 1) returns the revenue from the next month for each row. The second parameter (1) indicates the offset, meaning one row ahead. For the last row of the partition, LEAD returns NULL by default, unless a default value is specified as the third argument.

Question 9

How to calculate a running total of sales using a window function?

Accepted Answer

SUM(sales) OVER(ORDER BY date) calculates a running total because, by default, the window with ORDER BY uses the frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means that for each row, the sum includes all rows from the start of the partition to the current row. Adding PARTITION BY allows calculating running totals per group.

Question 10

What does SUM(sales) OVER() return without PARTITION BY or ORDER BY?

Accepted Answer

When OVER() is empty, the window spans the entire result set. SUM(sales) OVER() therefore returns the grand total of sales, repeated on every row. This is useful for calculating each row's percentage of the total, for example sales / SUM(sales) OVER() to get each sale's share of the overall total.

Question 11

How to use NTILE(4) and what does this function return?

Accepted Answer

NTILE(n) divides the rows of a partition into n approximately equal groups and assigns a group number (from 1 to n) to each row. NTILE(4) thus creates quartiles: the first quarter of rows receives 1, the second quarter receives 2, etc. If the row count is not divisible by n, the first groups contain one extra row. This is useful for segmenting data into percentiles.

Question 12

How to calculate a 3-month moving average with a window function?

Accepted Answer

AVG(revenue) OVER(ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) calculates the average of the last 3 months (current month and 2 preceding). The ROWS BETWEEN clause explicitly defines the window frame. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes exactly 3 rows. Moving averages are essential in trend analysis to smooth out seasonal variations.

Question 13

What is the difference between ROWS and RANGE in a window frame specification?

Accepted Answer

ROWS counts physical rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW always includes exactly 3 rows. RANGE compares logical values: RANGE BETWEEN 2 PRECEDING AND CURRENT ROW includes all rows whose ORDER BY value falls within an interval of 2 from the current row, which can include a variable number of rows when duplicates exist. RANGE is the default behavior with ORDER BY.

Question 14

What is the default frame when ORDER BY is specified in OVER() without an explicit frame?

Accepted Answer

When an ORDER BY is present in OVER() without an explicit frame specification, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means the calculation includes all rows from the start of the partition to the current row (and rows with the same ORDER BY value). This default behavior is why SUM() OVER(ORDER BY date) produces a running total.

Question 15

How to calculate the percentage of each sale relative to the total sales of its category?

Accepted Answer

The expression amount / SUM(amount) OVER(PARTITION BY category) divides each amount by the total for its category. PARTITION BY category makes SUM calculate the total per category, and this total is repeated on each row of the category. This is a very common window function use case: comparing an individual value to a group aggregate without needing a subquery or join.

Question 16

How to get the first and last value of a partition?

Accepted Answer

FIRST_VALUE(column) and LAST_VALUE(column) return the first and last value of the window respectively. Caution: with the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), LAST_VALUE returns the current row's value, not the last in the partition. To get the true last value, the frame must be explicitly defined with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Question 17

In what order are window functions evaluated in a SQL query?

Accepted Answer

Window functions are evaluated after the FROM, WHERE, GROUP BY and HAVING clauses, but before ORDER BY and LIMIT. This means a window function operates on the filtered and aggregated result. It is not possible to use a window function in a WHERE or HAVING clause. To filter on a window function result, a subquery or CTE must be used.

Question 18

How to calculate the percentage change in revenue compared to the previous month?

Accepted Answer

Combining the current value with LAG() enables computing the change: (revenue - LAG(revenue) OVER(ORDER BY month)) / LAG(revenue) OVER(ORDER BY month). LAG retrieves the previous month's revenue, the subtraction gives the absolute difference, and dividing by LAG gives the rate of change. Using NULLIF is recommended to avoid division by zero if the previous month has no revenue.

Question 19

How to filter the results of a window function, for example keeping only rank 1 per category?

Accepted Answer

Window functions cannot be used directly in a WHERE clause because they are evaluated after filtering. The solution is to wrap the query in a subquery or CTE, then filter on the window function result in the outer query. For example: WITH ranked AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY category ORDER BY amount DESC) AS rn FROM sales) SELECT * FROM ranked WHERE rn = 1.

Question 20

How to define a reusable named window with the WINDOW clause?

Accepted Answer

The WINDOW clause allows defining a reusable named window in the query. The syntax is WINDOW w AS (PARTITION BY category ORDER BY date), then reference this window with OVER w in each window function. This avoids duplication when multiple columns use the same window specification. The WINDOW clause is placed after HAVING and before ORDER BY in the query.

SQL - Window Functions

What is a window function in SQL?

Answer

Which clause is mandatory to define a window function?

Answer

What is the role of PARTITION BY in a window function?

Answer

What is the main difference between GROUP BY and PARTITION BY?

What does ROW_NUMBER() OVER(ORDER BY salary DESC) return?

What is the difference between RANK() and DENSE_RANK() in case of ties?

Master Data Analytics for your next interview