Bird
0
0

You want to find each employee's salary difference compared to the previous employee in the same department ordered by salary descending. Which query correctly uses window functions to achieve this?

hard📝 Application Q15 of 15
PostgreSQL - Window Functions in PostgreSQL
You want to find each employee's salary difference compared to the previous employee in the same department ordered by salary descending. Which query correctly uses window functions to achieve this?
ASELECT employee_id, department, salary, salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_diff FROM employees;
BSELECT employee_id, department, salary, salary - LEAD(salary) OVER (ORDER BY salary) AS salary_diff FROM employees;
CSELECT employee_id, department, salary, salary - LAG(salary) FROM employees GROUP BY department ORDER BY salary DESC;
DSELECT employee_id, department, salary, salary - FIRST_VALUE(salary) OVER (PARTITION BY department) AS salary_diff FROM employees;
Step-by-Step Solution
Solution:

  1. Step 1: Use LAG() to get previous row's salary per department

    LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) gets previous salary in descending order within department.

  2. Step 2: Subtract previous salary from current salary

    salary - LAG(salary) calculates difference compared to previous employee.

  3. Final Answer:

    SELECT employee_id, department, salary, salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_diff FROM employees; -> Option A

  4. Quick Check:

    LAG() gets previous row value for difference [OK]
Quick Trick: Use LAG() with PARTITION BY and ORDER BY for previous row values [OK]
Common Mistakes:
  • Using LEAD() instead of LAG() for previous row
  • Omitting PARTITION BY causing wrong grouping
  • Trying to use aggregate functions without OVER()

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes
Common Table Expressions - Recursive CTE for hierarchical data - Quiz 8hard Common Table Expressions - Recursive CTE for graph traversal - Quiz 15hard Common Table Expressions - CTE materialization behavior - Quiz 12easy Full-Text Search - Ranking with ts_rank - Quiz 6medium JSON and JSONB - Arrow operators (-> and ->>) - Quiz 12easy JSON and JSONB - Why JSON support matters in PostgreSQL - Quiz 1easy Joins in PostgreSQL - LEFT JOIN and RIGHT JOIN - Quiz 14medium Subqueries in PostgreSQL - Subqueries in FROM (derived tables) - Quiz 1easy Subqueries in PostgreSQL - LATERAL subqueries - Quiz 11easy Window Functions in PostgreSQL - LAG and LEAD for row comparison - Quiz 4medium