Explain the concept of window functions in SQL. Provide examples to illustrate their usage.
Answer:
Window Functions:
Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not group rows into a single output row; instead, they return a value for each row in the query result.
Types of Window Functions:
1. Aggregate Window Functions: Compute aggregate values like SUM, AVG, COUNT, etc.
2. Ranking Window Functions: Assign a rank to each row, such as RANK(), DENSE_RANK(), and ROW_NUMBER().
3. Analytic Window Functions: Perform calculations like LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE().
Syntax:
SELECT column_name,
window_function() OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;
Examples:
1. Using ROW_NUMBER():
Assign a unique number to each row within a partition of the result set.
SELECT employee_name, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
This query ranks employees within each department based on their salary in descending order.
2. Using AVG() with OVER():
Calculate the average salary within each department without collapsing the result set.
SELECT employee_name, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;
This query returns the average salary for each department along with each employee's salary.
3. Using LEAD():
Access the value of a subsequent row in the result set.
SELECT employee_name, department_id, salary,
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
FROM employees;
This query retrieves the salary of the next employee within the same department based on the current sorting order.
4. Using RANK():
Assign a rank to each row within the partition, with gaps in the ranking values if there are ties.
SELECT employee_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
This query ranks employees within each department by their salary in descending order, leaving gaps for ties.
Tip: Window functions are powerful for performing calculations across a set of rows while retaining the individual rows. They are useful for running totals, moving averages, ranking, and accessing data from other rows within the same result set.
Go though SQL Learning Series to refresh your basics
Share with credits: https://t.me/sqlspecialist
Like this post if you want me to continue SQL Interview Preparation Series 👍❤️
Hope it helps :)