MS SQL difference between two latest records Given table is : 

create table events (
      event_type integer not null,
      value integer not null,
      time timestamp not null,
      unique(event_type, time)
  );


need to return difference of value for event_type between latest and second latest registered.
You can use lead() window function to get next record value (order by time desc for each event). row_number()=1 will give you just the first row per event_type

select t1.event_type, t1.value - t1.prev_value as diff
  from (
     select t.event_type,
            t.value,
            lead(t.value) over (partition by t.event_type order by time desc) prev_value,
            row_number() over (partition by t.event_type order by time desc) rn
       from events t) as t1
where rn=1
  and prev_value is not null-- this will prevent event_types with just one row