SQL Interview Questions

Window functions in SQL perform calculations across a set of table rows that are related to the current row. They provide more flexibility than traditional aggregate functions because they do not require grouping of results into a single output row per group. Instead, they allow you to perform calculations across a window of rows without collapsing the result set.

Syntax

The basic syntax of a window function is as follows:

function_name() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

• function_name: The window function to be applied (e.g., ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), etc.).
• PARTITION BY: Optional. Divides the result set into partitions to which the function is applied.
• ORDER BY: Optional. Defines the order of rows in each partition.
• frame_clause: Optional. Defines the subset of rows used to calculate the function's result.

Common Window Functions

1. ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
2. RANK(): Assigns a rank to rows within a partition, with gaps in ranking for ties.
3. DENSE_RANK(): Assigns a rank to rows within a partition without gaps in ranking for ties.
4. NTILE(): Distributes rows into a specified number of approximately equal buckets.
5. LEAD(): Accesses data from the subsequent row in the same result set.
6. LAG(): Accesses data from the preceding row in the same result set.
7. SUM(): Computes the sum of a column’s values within a window.
8. AVG(): Computes the average of a column’s values within a window.
9. MAX(): Computes the maximum value of a column within a window.
10. MIN(): Computes the minimum value of a column within a window.

Examples

Example 1: ROW_NUMBER()

Assign a unique row number to each row within each department.

SELECT employee_id, department_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

Example 2: RANK() and DENSE_RANK()

Rank employees within each department by salary, showing gaps for ties (RANK) and without gaps for ties (DENSE_RANK).

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

Example 3: LEAD() and LAG()

Compare the current employee's salary with the previous and next employee's salary within each department.

SELECT employee_id, department_id, salary,
       LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary,
       LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
FROM employees;

Example 4: SUM() with a Window Frame

Calculate a running total of salaries within each department.

SELECT employee_id, department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

Using Window Functions Without PARTITION BY

If you do not specify the PARTITION BY clause, the window function is applied to the entire result set.

Example: Calculate the rank of employees by salary across the entire company.

SELECT employee_id, department_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Using Window Functions with ORDER BY

The ORDER BY clause within the OVER clause defines the logical order of the rows for window functions.

Example: Calculate the cumulative sum of salaries ordered by employee ID.

SELECT employee_id, department_id, salary,
       SUM(salary) OVER (ORDER BY employee_id) AS cumulative_sum
FROM employees;

Conclusion

Window functions in SQL are powerful tools for performing calculations across sets of rows related to the current row without collapsing the result set. They provide a flexible and efficient way to perform a variety of analytical tasks, including ranking, cumulative calculations, and accessing data from previous or next rows. By mastering window functions, you can write more concise and powerful SQL queries that provide deeper insights into your data.