Window Functions

Question 1

Which clauses can a window function be called in?

Answer 1

The SELECT and ORDER BY clauses

Question 2

What is the difference between a window function and GROUP BY?

Question 3

What does this query do?

SELECT first_name, department, salary, 
LEAD(first_name) OVER (ORDER BY salary ASC) prev_sal_name
FROM employees

Answer 3

It returns first_name, department, salary, and the first name of the person with the next highest salary out of all employees.

Question 4

What does this query do?

SELECT first_name, department, salary, 
LAG(first_name) OVER (PARTITION BY department ORDER BY salary ASC) prev_salary
FROM employees

Answer 4

It returns theIt returns first_name, department, salary, and the first name of the person with the next lowest salary from the employee’s respective department.

Question 5

What types of functions are LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE()?

Answer 5

Positional functions

Question 6

What is a critical part of a window function when using positional functions?

Answer 6

ORDER BY, because positional functions are entirely dependent on the order of the values within the window

Question 7

T/F: using OVER() without any info inside of its parentheses will fail

Answer 7

False, it will run on the entire data set as its partition

Question 8

What is the purpose of the PARTITION BY clause in this query?

SELECT 
MAX(salary) OVER (PARTITION BY department) max_salary
FROM employees

Answer 8

It specifies the window in which to aggregate the data. In this case, it is specifying ‘department’ as the partition over which to aggregate salary.