You want to compare each employee's salary with the salary two rows before them ordered by hire_date. Which query correctly achieves this?

hard📝 Application Q8 of 15

PostgreSQL - Window Functions in PostgreSQL

ASELECT employee_id, salary, LAG(salary, 2) OVER (ORDER BY hire_date) AS salary_two_before FROM employees;

BSELECT employee_id, salary, LEAD(salary, 2) OVER (ORDER BY hire_date) AS salary_two_before FROM employees;

CSELECT employee_id, salary, LAG(salary) OVER (PARTITION BY hire_date) AS salary_two_before FROM employees;

DSELECT employee_id, salary, LAG(salary, 2) FROM employees ORDER BY hire_date;

Step-by-Step Solution

Solution:

Step 1: Identify correct function and offset
LAG with offset 2 gets value two rows before in the order specified.
Step 2: Check query correctness
SELECT employee_id, salary, LAG(salary, 2) OVER (ORDER BY hire_date) AS salary_two_before FROM employees; uses LAG(salary, 2) OVER (ORDER BY hire_date), which is correct. SELECT employee_id, salary, LEAD(salary, 2) OVER (ORDER BY hire_date) AS salary_two_before FROM employees; uses LEAD (wrong direction). SELECT employee_id, salary, LAG(salary) OVER (PARTITION BY hire_date) AS salary_two_before FROM employees; partitions by hire_date (likely one row per partition). SELECT employee_id, salary, LAG(salary, 2) FROM employees ORDER BY hire_date; misses OVER clause.
Final Answer:
SELECT employee_id, salary, LAG(salary, 2) OVER (ORDER BY hire_date) AS salary_two_before FROM employees; -> Option A
Quick Check:
LAG with offset 2 and ORDER BY hire_date is correct [OK]

Quick Trick: Use LAG with offset and ORDER BY for previous rows comparison [OK]

Common Mistakes:

Master "Window Functions in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free