I have a snapshot file of employees with record dates and career levels. The file is updated on a daily basis

Question

I'm trying to understand when was the last date an employee moved from one level to another (from level B to level C for instance) while ignoring the changes due to company reorg.

Meaning if someone moved from band 1 to band 3 then to level a , I'd like to record the first move only (band 1 to 3). But if someone moved from level A to B then to C , I'd like to record the move from B to C. Below is the code I've tried.

df1 <- data.table(snap)
setorderv(df1, c("workday_id", "record_date"))
df1[, career_level_lagged := shift(career_level, n=1, type="lag"), by= "workday_id"]
df1 <- df1[career_level_lagged != career_level]

# identify latest row for each emp
df1[, c("row", "number_rows") := list(frank(record_date), .N), by="workday_id"]
df1 <- df1[row==number_rows]

The code picks employees with and without career changes too. Which is not what I want.