Probably the problem can be solved by some joins, but if you prefer OVER, then check this statement:
update t
set t.Effective_End_Date = dateadd(second, -4, d1)
from (
select *,
lag(Effective_Start_Date, 1) over (partition by ID order by Effective_Start_Date desc) d1,
lag(Effective_Start_Date, 2) over (partition by ID order by Effective_Start_Date desc) d2
from MyTable
) t
where d1 is not null and d2 is null
Although, the ROW_NUMBER is an attractive function too.