SQL how can i detect if a value decrease over time?

Hi, how can i check who are the employees whose salary has fallen ?

:

SELECT employees.emp_no, first_name, last_name, salary, from_date, to_date, hire_date 
from employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no;

I only want to fetch the name of employees whose salary has fallen

Answer

You can use the positional analytic function LAG() to find these rows. This is a standard SQL function that peeks at a previous row, according to a specific criteria.

For example:

select emp_no, first_name, last_name
from (
  select
    e.*,
    s.salary,
    lag(s.salary) over(partition by e.emp_no order by from_date) as prev_salary
  from employees e
  join salaries s on s.emp_no = e.emp_no
) x
where salary < prev_salary