LEAD() and LAG() Functions

The LEAD() and LAG() functions allow users to access data from subsequent or preceding rows within the same result set. This capability is invaluable for comparative analysis and trend evaluation. For example, to compare each employee’s salary to the previous employee’s salary, you might use:

sqlCopy codeSELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS previous_salary FROM employees;

In this case, LAG(salary) retrieves the salary of the employee hired just before the current employee based on hire date. Conversely, LEAD() can be used to access future rows. These functions enhance analytical capabilities by enabling direct comparisons of current and previous or future values, facilitating trend analysis and performance evaluations.