Scribd
Upload
2 views4 pages

SQL Window Functions Answer

The document provides SQL window functions categorized into four sections: Ranking Functions, Aggregate Window Functions, Value Functions, and Advanced Window Functions. Each section includes example SQL queries demonstrating how to use various window functions such as ROW_NUMBER(), RANK(), SUM(), LAG(), and LEAD() to analyze employee salary data. The examples cover tasks like ranking salaries, calculating totals and averages, and finding specific salary values within departments.

Uploaded by

itsnikhils45
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views4 pages

SQL Window Functions Answer

The document provides SQL window functions categorized into four sections: Ranking Functions, Aggregate Window Functions, Value Functions, and Advanced Window Functions. Each section includes example SQL queries demonstrating how to use various window functions such as ROW_NUMBER(), RANK(), SUM(), LAG(), and LEAD() to analyze employee salary data. The examples cover tasks like ranking salaries, calculating totals and averages, and finding specific salary values within departments.

Uploaded by

itsnikhils45
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Window Functions – Answer Key

Section A: Ranking Functions

1. ROW_NUMBER()

SELECT emp_name, salary,


ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_num
FROM employees;

2. RANK()

SELECT emp_name, salary,


RANK() OVER(ORDER BY salary DESC) AS rank_val
FROM employees;

3. DENSE_RANK()

SELECT emp_name, salary,


DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_rank_val
FROM employees;

4. Row number within each department

SELECT emp_name, department,


ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

5. Rank within each department

SELECT emp_name, department,


RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank_val
FROM employees;

Section B: Aggregate Window Functions

6. Total salary of all employees

SELECT emp_name, salary,


SUM(salary) OVER() AS total_salary
FROM employees;

7. Total salary per department

SELECT emp_name, department, salary,


SUM(salary) OVER(PARTITION BY department) AS dept_total
FROM employees;
8. Average salary per department

SELECT emp_name, department, salary,


AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees;

9. Max salary per department

SELECT emp_name, department, salary,


MAX(salary) OVER(PARTITION BY department) AS dept_max
FROM employees;

10. Min salary per department

SELECT emp_name, department, salary,


MIN(salary) OVER(PARTITION BY department) AS dept_min
FROM employees;

Section C: Value Functions

11. LAG() – previous salary

SELECT emp_name, salary,


LAG(salary) OVER(ORDER BY salary) AS prev_salary
FROM employees;

12. LEAD() – next salary

SELECT emp_name, salary,


LEAD(salary) OVER(ORDER BY salary) AS next_salary
FROM employees;

13. FIRST_VALUE()

SELECT emp_name, department, salary,


FIRST_VALUE(salary) OVER(PARTITION BY department ORDER BY salary DESC) AS first_salary
FROM employees;

14. LAST_VALUE()

SELECT emp_name, department, salary,


LAST_VALUE(salary) OVER(
PARTITION BY department
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_salary
FROM employees;
15. Replace NULL using LEAD()

SELECT emp_name,
COALESCE(salary, LEAD(salary) OVER(ORDER BY emp_id)) AS updated_salary
FROM employees;

Section D: Advanced Window Functions

16. Running total (cumulative salary)

SELECT emp_name, joining_date, salary,


SUM(salary) OVER(ORDER BY joining_date) AS running_total
FROM employees;

17. Salary difference (current - previous)

SELECT emp_name, salary,


salary - LAG(salary) OVER(ORDER BY salary) AS salary_diff
FROM employees;

18. Percentage contribution

SELECT emp_name, department, salary,


salary * 100 / SUM(salary) OVER(PARTITION BY department) AS percent_contribution
FROM employees;

19. Second highest salary in each department

SELECT emp_name, department, salary


FROM (
SELECT emp_name, department, salary,
DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)t
WHERE rnk = 2;

20. Highest salary in each department

SELECT emp_name, department, salary


FROM (
SELECT emp_name, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)t
WHERE rnk = 1;

You might also like