First Value and second last value in SQL

Question

I have a table with 4 columns: worker_id,month,basic_salary. I am trying to write a script that return worker_id, last salary date, second last basic_salary date, last salary amount (from basic_salary column).

Here's the script i wrote:

with cte as (
  select WORKER_ID,  (select max(month) from workers_table) last_salary
    , ROW_NUMBER()over (partition by worker_id order by month) rn
  from workers_table
  group by WORKER_ID,month
)
select *
from cte 
where rn=2

Here is a sample of the data:

enter image description here

and the data I get is:

WORKER_ID   last_salary rn
2011-11-11  2022-01-04  2
2011-11-12  2022-01-04  2
2011-11-13  2022-01-04  2
2011-11-14  2022-01-04  2
2011-11-15  2022-01-04  2
2011-11-16  2022-01-04  2

The last_salary column is incorrect and i couldn't find a way to fix it and get the right results.

Answer 1

You can use the LEAD window function to get the next row in the set

WITH cte AS (
    SELECT
      WORKER_ID,
      month,
      basic_salary,
      LEAD(month) OVER (PARTITION BY worker_id ORDER BY month) AS SecondLastSalaryDate,
      ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month) AS rn
    FROM WORKERS_TABLE
)
SELECT
  c.WORKER_ID,
  c.month AS LastSalaryDate,
  c.basic_salary As Last_basic_salary,
  c.SecondLastSalaryDate
FROM cte c
WHERE c.rn = 1;