Select Difference Between Rows (Postgres)

Question

i'm aggregating facebook "like" counts for urls over time. each hour, i check the "like" count and insert it into a timestamped row. how can i get the difference in total between the new row and the previous hour's row?

eg,

1. insert total=5 for id=1 at 2pm
2. insert total=12, diff_since_last=7 for id=1 at 3pm

thanks!

Answer 1

This should do it.

SELECT id, 
       total, 
       total - lag(total) over (partition by id order by timestamp_column) as diff_since_last
FROM the_table_with_no_name