What is the usage of the Oracle lag function?

The LAG function is a window function in Oracle SQL that is used to retrieve the value of a specified row count before the current row.

The basic syntax is as follows:

LAG (expr, offset, default) OVER (PARTITION BY partition_col ORDER BY order_col)

1. expr: Column or expression from which to retrieve the value
2. Number of lines to be retrieved, can be a negative number to indicate lines after the current line.
3. If a corresponding value is not found, the default value will be returned.
4. PARTITION BY: Optional parameter used for partitioning data.
5. Sort by: Specify the column to be sorted.

For example, if there is a table named employees with fields employee_id, last_name, hire_date, and salary, and you want to query the hire date and the most recent salary raise date for each employee, you can use the LAG function.

SELECT employee_id, last_name, hire_date, salary,
       LAG(hire_date, 1, NULL) OVER (ORDER BY hire_date) AS prev_hire_date,
       LAG(salary, 1, NULL) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

This will return the hiring date and the last salary adjustment date for each employee.