Find last job change date with JOB_TITLE and EVENT_DATE

Question

Hi I am working in an Azure Databricks and I am looking for a SQL query solution.

Assuming that my db has five columns:

ID	EVENT_DATE	JOB_TITLE	PAY
12345	2021-01-01	VP1	100,000
12345	2020-01-10	VP1	90,000
12345	2019-01-20	Analyst1	80,000
12346	2021-02-01	VP2	200,000
12346	2020-02-10	Analyst2	150,000
12346	2020-01-20	Analyst2	110,000

Basically I want the EVENT_DATE when JOB_TITLE changed the last time. This is my desired output:

ID	JOB_TITLE	PAY	LAST_JOB_CHANGE_DATE
12345	VP1	90,000	2021-01-10
12346	VP2	200,000	2021-02-01

For the last column LAST_JOB_CHANGE_DATE, we are pulling from the 2nd and 4th row of the table because that's the date when they changed job the last time.

Thank you!

user_9220093 · Answer

There's probably a simpler solution for this but the following should work.

I'm assuming you wanted the MOST resent job change for each employee. To illustrate this, I added an extra row for an Engineer1. The ROW_NUMBER() window function helps us with this.

ID	EVENT_DATE	JOB_TITLE	PAY
12345	2021-01-01	VP1	100,000
12345	2020-01-10	VP1	90,000
12345	2019-01-20	Analyst1	80,000
12345	2018-01-04	Engineer1	75,000
12346	2021-02-01	VP2	200,000
12346	2020-02-10	Analyst2	150,000
12346	2020-01-20	Analyst2	110,000

Here is the query:

SELECT <---- (4)
c.ID,
c.JOB_TITLE,
c.PAY,
c.last_job_change_date
FROM
(
  SELECT <---- (3)
  b.ID,
  ROW_NUMBER() OVER (PARTITION BY b.ID ORDER BY b.last_job_change_date DESC) AS row_id,
  b.JOB_TITLE,
  b.PAY,
  b.last_job_change_date
  FROM
  (
    SELECT <---- (2)
    a.ID,
    a.JOB_TITLE,
    a.PAY,
    a.EVENT_DATE as last_job_change_date
    FROM
    (
      SELECT <---- (1)
      ID,
      EVENT_DATE,
      PAY,
      JOB_TITLE,
      LEAD(JOB_TITLE, 1) OVER (
          PARTITION BY ID ORDER BY EVENT_DATE DESC) job_change
      FROM yourtable
    ) a
    WHERE JOB_TITLE <> job_change
  ) b
) c
WHERE row_id = 1

I used a 4 step process and annotated the query with each step:

Returns a table with a column for the subsequent job title (ordered by most recent title) of each employee.
Returns the table from (1) but removes rows where the employee did not change their job
Add row numbers so we can get the most recent job change of each employee
Return most recent job changes for each employee

user_1527504 · Answer

You can just use INNER JOIN to accomplish that, ie

%sql
SELECT a.*
FROM yourTable a 
  INNER JOIN 
  ( 
  SELECT id, MAX(event_date) event_date 
  FROM yourTable b
  GROUP BY id
  ) b ON a.id = b.id 
  AND a.event_date = b.event_date

The ROW_NUMBER approach would also work well:

%sql
WITH cte AS
(
SELECT
  ROW_NUMBER() OVER( PARTITION BY id ORDER BY event_date DESC  ) AS rn, 
  *
FROM yourTable a 
)
SELECT *
FROM cte
WHERE rn = 1

My results:

Find last job change date with JOB_TITLE and EVENT_DATE

2 Answers