title: Difference between last and second last event in a table of events
body: <p>I have the following table 
  <a href="https://i.stack.imgur.com/GucZZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GucZZ.png" alt="Events table"></a></p>
  
  <p>which created by</p>
  
  <pre><code>create table events (
      event_type integer not null,
      value integer not null,
      time timestamp not null,
      unique (event_type, time)
  );
  </code></pre>
  
  <p>given the data in the pic, 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.</p>
  
  <p>Given the above data, the output should be like </p>
  
  <pre><code>event_type value
  2          -5
  3           4
  </code></pre>
  
  <p>I solved it using the following :</p>
  
  <pre><code>CREATE VIEW [max_date] AS
  SELECT event_type, max(time) as time, value
  FROM events
  group by event_type
  having count(event_type) &gt;1
  order by time desc;
  
  
  select event_type, value
  from
  (
      select event_type, value, max(time)
      from(
           Select E1.event_type, ([max_date].value - E1.value) as value, E1.time
           From events E1, [max_date]
           Where [max_date].event_type = E1.event_type
           and [max_date].time &gt; E1.time
          )
      group by event_type
   )
  </code></pre>
  
  <p>but this seems like a very complicated query and I wonder if there is an easier way? </p>