SQL - Advanced Window Functions
You want to compare each employee's salary with the salary of the employee hired immediately before them. Which query correctly uses
LAG assuming hire_date exists?You want to compare each employee's salary with the salary of the employee hired immediately before them. Which query correctly uses LAG assuming hire_date exists?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Identify correct ordering for previous hire
To get the previous employee by hire date, order rows byhire_date.Step 2: Check correct LAG usage
SELECT id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM Employees; usesLAG(salary) OVER (ORDER BY hire_date), which correctly fetches previous salary by hire date order.Step 3: Eliminate incorrect options
SELECT id, salary, LAG(salary) OVER (PARTITION BY hire_date) AS prev_salary FROM Employees; wrongly partitions by hire_date, SELECT id, salary, LAG(salary) OVER (ORDER BY id) AS prev_salary FROM Employees; orders by id not hire_date, SELECT id, salary, LAG(salary, 1) FROM Employees ORDER BY hire_date; misses OVER clause.Final Answer:
SELECT id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM Employees; -> Option CQuick Check:
LAG with ORDER BY hire_date gets previous hire's salary [OK]
Quick Trick: Order by hire_date to get previous employee's salary [OK]
Common Mistakes:
- Using PARTITION BY instead of ORDER BY
- Ordering by wrong column
- Omitting OVER clause
Master "Advanced Window Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes