Finding the difference between the latest and the second latest term

Question

The structure of the table is like

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

Have inserted few values like

insert into events values
(2,   5,  '2015-05-09 12:42:00'),
(4, -42,  '2015-05-09 13:19:57'),
(2,   2,  '2015-05-09 14:48:39'),
(2,   7,  '2015-05-09 13:54:39'),
(3,  16,  '2015-05-09 13:19:57'),
(3,  20,  '2015-05-09 15:01:09') 

I want to write a query that for each event_type that has been registered more than once returns the difference between the latest and the second latest value .

Given the above data, the output should be like

event_type value
2          -5
3           4

I can bring out the lowest and the highest using below queries . This is how far I could reach ..confused on the way further .

select event_type,value,time from events group by event_type order by event_type desc;

select * from events group by event_type order by event_type ;

Answer 1

With Common table expressions:

WITH evt AS
(
  select
    event_type,
    value,
    rank() over(partition by event_type order by time desc) rnk
  from events
)
select
    t1.event_type, t1.value - t2.value
from evt t1, evt t2
where t1.event_type = t2.event_type
and t1.rnk = 1
and t2.rnk = 2

Without any CTE

select t1.event_type, t1.value - t2.value from
(
select
    event_type,
    value,
    rank() over(partition by event_type order by time desc) rnk
from events
)t1,
(
select
    event_type,
    value,
    rank() over(partition by event_type order by time desc) rnk
from events
)t2
where t1.event_type = t2.event_type
and t1.rnk = 1
and t2.rnk = 2
;