Arun Arun - 5 months ago 22
SQL Question

SQL to calculate difference between 2 latest recent values by event_types

The events table looks like

event_type value timestamp
2 2 06-06-2016 14:00:00
2 7 06-06-2016 13:00:00
2 2 06-06-2016 12:00:00
3 3 06-06-2016 14:00:00
3 9 06-06-2016 13:00:00
4 9 06-06-2016 13:00:00


My goal is to filter event types that occur more than twice and subtract most two recent values and shows BY event_type.

The end result would be

event_type value
2 -5
3 -6


I was able to get filter events occurred more than twice and order by event_type based on timestamp desc.
The difficult part for me is to subtract most two recent values and shows BY event_type.

DB / SQL experts , please help

Answer

You can use a query like this:

SELECT event_type, diff
FROM (
  SELECT event_type, value, "timestamp", rn,
         value - LEAD(value) OVER (PARTITION BY event_type 
                                  ORDER BY "timestamp" DESC) AS diff
  FROM (
    SELECT event_type, value, "timestamp",
           COUNT(*) OVER (PARTITION BY event_type) AS cnt,
           ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY "timestamp" DESC) AS rn
    FROM mytable) AS t
  WHERE cnt >=2 AND rn <= 2 ) AS s
WHERE rn = 1  

The innermost subquery uses:

  • Window function COUNT with PARTITION BY clause, so as to calculate the population of each event_type slice.
  • Window function ROW_NUMBER so as to get the two latest records within each event_type slice.

The mid-level query uses LEAD window function, so as to calculate the difference between the first and the second records. The outermost query simply returns this difference.

Demo here