Oracle dates diffs using analytics

Simple little analytical query to find out the difference between two dates on adjecent rows in oracle using the lead analytical function lead() fetches the value from the next row in the resultset, you can use lag() to find the previous row.

SQL> select * from leadtest;

        ID TAG        STAMP
---------- ---------- ---------
         1 dog        05-JAN-06
         2 dog        05-MAY-06
         3 dog        05-SEP-06
         4 cat        05-APR-06
         5 cat        28-APR-06
         6 cat        28-SEP-06
         7 cat        28-OCT-06

7 rows selected.

SQL> with t as
  2  (
  3    select
  4      tag,
  5      stamp,
  6      lead(stamp,1,stamp) over 
  7        (partition by tag order by stamp) stamp2
  8    from
  9      leadtest
  10  )
  11    select tag, stamp, stamp2, stamp2 - stamp diff from t
  12 /

TAG        STAMP     STAMP2          DIFF
---------- --------- --------- ----------
cat        05-APR-06 28-APR-06         23
cat        28-APR-06 28-SEP-06        153
cat        28-SEP-06 28-OCT-06         30
cat        28-OCT-06 28-OCT-06          0
dog        05-JAN-06 05-MAY-06        120
dog        05-MAY-06 05-SEP-06        123
dog        05-SEP-06 05-SEP-06          0

7 rows selected.

-- Lets try lag() as well.
SQL> with t as
  2  (
  3    select
  4      tag,
  5      stamp,
  6      lag(stamp,1,stamp) over 
  7        (partition by tag order by stamp) stamp2
  8    from
  9      leadtest
  10  )
  11    select tag, stamp, stamp2, stamp - stamp2 diff from t
  12 /

TAG        STAMP     STAMP2          DIFF
---------- --------- --------- ----------
cat        05-APR-06 05-APR-06          0
cat        28-APR-06 05-APR-06         23
cat        28-SEP-06 28-APR-06        153
cat        28-OCT-06 28-SEP-06         30
dog        05-JAN-06 05-JAN-06          0
dog        05-MAY-06 05-JAN-06        120
dog        05-SEP-06 05-MAY-06        123

7 rows selected.