Previous day of last day

Question

I have a table of Promotion where a employee has given four promotions. I want to find out the previous promotion (3th) of last promotions date .

Answer 1

This returns, for every employee their 2nd last promotion date.

select *
  from (
select *,
       row_number() over (partition by employee order by promoted_date desc) rn
  from Promotion
       ) numbered
 where rn=2;