Write a query to calculate the difference between current row and previous row's salary (lag function).

To calculate the difference between the current row's salary and the previous row's salary, you use the LAG() window function. LAG() allows you to look backward at a previous row's data within your result set without executing a complex self-join.

The Query

Here is the standard SQL query. It orders the employees by salary (highest to lowest) and computes the exact structural difference between adjacent positions:

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Step 1: Fetch the previous row's salary
    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS previous_salary,
    
    -- Step 2: Subtract the previous row's salary from the current row's salary
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_difference
FROM employees;

Use code with caution.

Syntax Deep Dive: LAG(column, offset, default)

• salary: The target column you want to retrieve a historical value from.
• 1 (Offset): How many rows to look backward. A value of 1 targets the immediate previous row.
• 0 or salary (Default Value): What to return if there is no previous row (e.g., the very first row in the dataset).
  • Passing 0 or omitting it helps show a blank baseline.
  • Passing the column itself (salary) ensures that subtracting it from the current row results in a clean 0 instead of a breaking NULL value on row one.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the calculations live:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Diana Prince', 'Marketing', 82000.00),
(4, 'Fiona Gallagher', 'Engineering', 110000.00),
(5, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE LAG QUERY
SELECT 
    employee_name,
    department,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_employee_salary,
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_diff_from_previous
FROM employees;

Use code with caution.

Expected Query Output

employee_name	department	salary	previous_employee_salary	salary_diff_from_previous
Fiona Gallagher	Engineering	110000.00	NULL	0.00
Alice Smith	Engineering	95000.00	110000.00	-15000.00
Diana Prince	Marketing	82000.00	95000.00	-13000.00
Bob Jones	Sales	75000.00	82000.00	-7000.00
Hannah Abbott	Marketing	68000.00	75000.00	-7000.00

(Note: Fiona displays 0.00 difference because she is at the top of the chain; there is no previous record to subtract from her total.)