Find how many times the department of an employee has changed

I have the following table Employee storing any updates made on an employee:

EmployeeId DepartmentId Status       From       To 
44         30           Recruited    01/01/2017 06/03/2017
44         56           IN           07/03/2017 07/03/2018
44         67           IN           06/05/2018 06/09/2018
44         33           IN           07/09/2018 02/02/2019
44         33           OUT          03/02/2019 31/12/2019
44         45           Recruited    01/02/2020 03/02/2020
44         45           IN           04/02/2020 NULL

I want to count how many times each employee has changed his department knowing that the employee life cycle is like below : Recuited – IN – OUT and the employees that left the company then went back to it like in this example.

Answer

I’m not sure what “Recruited”, “In” and “Out” have to do with this. If each row represents a period of time when an employee was in a department, then use lag() to measure changes:

select employeeId, count(*)
from (select t.*,
             lag(departmentId) over (partition by employeeId order by from_date) as prev_departmentId
      from t
     ) t
where prev_departmentId is null or prev_departmentId <> departmentId
group by employeeId;