You want to find all employees whose salary increased compared to the previous employee ordered by hire_date. Which query correctly uses LAG to achieve this?
hard📝 Application Q15 of 15
PostgreSQL - Window Functions in PostgreSQL
You want to find all employees whose salary increased compared to the previous employee ordered by hire_date. Which query correctly uses LAG to achieve this?
ASELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary;
BSELECT employee_id, salary FROM employees WHERE salary > (SELECT LAG(salary) FROM employees ORDER BY hire_date);
CSELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees WHERE salary > prev_salary;
DSELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees HAVING salary > prev_salary;
Step-by-Step Solution
Solution:
Step 1: Understand LAG usage for filtering
Window functions like LAG cannot be used directly in WHERE clause; use subquery or CTE to compute then filter.
Step 2: Analyze options
SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; correctly uses subquery. The second option's subquery LAG lacks OVER clause (syntax error). The third uses alias in WHERE (invalid). The fourth uses HAVING without GROUP BY (invalid).
Final Answer:
SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; -> Option A
Quick Check:
Subquery to filter on window functions [OK]
Quick Trick:Use subquery to filter on window function results [OK]
Common Mistakes:
Using alias in WHERE clause
Misusing HAVING without GROUP BY
Incorrect subquery for LAG
Master "Window Functions in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
