Get employees who received a raise in 2 consecutive years-CodePudding

I am trying to Get employees who received a raise in 2 consecutive years, in this case employee 1000 is the right answer.

here is the data and the sql i have tried.

EID	SALARY	YEAR
1000	10,000	2016
1000	7,500	2015
1000	6,000	2014
1001	8,000	2016
1001	7,500	2015
1002	7,500	2016
1002	7,500	2015
1002	5,000	2014
1003	6,000	2016
1003	7,000	2015
1003	5,000	2014

i have used following code however it gets only row number by EID and not calcualtion of last year and present year, i need to find employee who got raise in 2 consecutive years.

output

select * , 
       row_number() over(partition by eid order by salary and year desc)as rn 
from gs;

CodePudding user response：

You can do it using the LEAD window function that compute the two consecutive previous value of the salary. Then you can check how many employees have at least one row with salary1 < salary2 < salary3.

SELECT DISTINCT 
    eid
FROM (
    SELECT
        eid,
        year,
        salary,
        (LEAD(salary, 1) OVER(PARTITION BY eid ORDER BY year DESC)) AS prev_salary1,
        (LEAD(salary, 2) OVER(PARTITION BY eid ORDER BY year DESC)) AS prev_salary2
    FROM 
        employees
    ) consecutive3
WHERE 
    salary > prev_salary1
AND     
    prev_salary1 > prev_salary2

The assumption is that there are no missing years for which a salary of a dependent was not recorded.

Here's the fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c0d8a1deec8e77bb32a173656c3e386.

EDIT: Detailed explanation

Let's do the example of Jennifer, who has worked for five years and got these salaries:

2018 -> 65000
2017 -> 55000
2016 -> 50000

She's a candidate for being selected as her salary was raised three times consecutively.

1. LEAD(salary, 1) OVER(PARTITION BY eid ORDER BY year DESC)

Allows you to get the salary for year "Y" and the salary for year "Y-1":

("year" -> "salary", "previous_salary")

2018 -> 65000 , 55000
2017 -> 55000 , 50000
2016 -> 50000 , NULL

2. LEAD(salary, 2) OVER(PARTITION BY eid ORDER BY year DESC)

Allows you to get the salary for year "Y" and the salary for year "Y-1":

("year" -> "salary", "previous_salary", "previous_salary_by_2_years")

2018 -> 65000 , 55000 , 50000
2017 -> 55000 , 50000 , NULL
2016 -> 50000 , NULL , NULL

3. WHERE salary > prev_salary1 AND prev_salary1 > prev_salary2

Some filtering on the employees who

have their year3 salary higher than their year2 salary (salary > prev_salary1)
have their year2 salary higher than their year1 salary (prev_salary1 > prev_salary2)

CodePudding user response：

I know that this has already been answered but here is my take using the lag function to determine if there was an increase from the previous year and ran that twice.

SELECT *
FROM (
    SELECT
        t2.*,
        LAG(increase) over (partition by eid order by year) AS increaseNextYear
    FROM (
        SELECT
            t1.*,
            COALESCE(salary - LAG(salary) over (partition by eid order by year), 0) > 0 AS increase
        FROM tbl_test t1
    ) as t2
) t3 where increase AND increaseNextYear

CodePudding user response：

with
  dates as
(
  select
    a.*,
    dense_rank() OVER (
      partition by eid
          order by year desc, salary
      )
        as rn, 
    case
      when 
        lead(salary,2)over(partition  by eid order by year, salary)
        >
        lead(salary,1)over(partition by eid order by year, salary)
      and  
        lead(salary,1)over(partition by eid order by year, salary)
        >
        salary
      then
        1
      else
        0
    end
      as flag
  from
    employees a
)
select
  eid
from
  dates
where
      rn   = 3
  and flag = 1

CodePudding user response：

Not a beautiful query, but straight-forward: find employees who had a salary in a year where the salary in the previous year was lower and the salary in the year before that even lower. Using LAG is more elegant, but I thought I'd throw this in, just to show an alternative.

select *
from employee
where exists
(
  select null
  from gs
  where gs.eid = employee.id
  and exists
  (
    select null
    from gs prev
    where prev.eid = gs.eid
    and prev.year = gs.year - 1
    and prev.salary < gs.salary
    and exists
    (
      select null
      from gs prevprev
      where prevprev.eid = prev.eid
      and prevprev.year = prev.year - 1
      and prevprev.salary < prev.salary
    )
  )
);

Same thing with a join:

select *
from employee
where exists
(
  select null
  from gs
  join gs prev on prev.eid = gs.eid
              and prev.year = gs.year - 1
              and prev.salary < gs.salary
  join gs prevprev on prevprev.eid = prev.eid
                  and prevprev.year = prev.year - 1
                  and prevprev.salary < prev.salary
  where gs.eid = employee.id
);