How can use lag to return the difference between the value of the current row and the value of the following row?

I would like to ask you how to return the difference between the value of the current row and the value of the following row. I know that I could use lead, but I would like to try to use lag function to do the same.

What I thought was lag(value) over (partition by id order by time desc), but I am not completely sure that this works.

Any suggestions?

Answer

Your query should work with the desc on the sort.

You should understand that SQL tables represent unordered sets. There is no ordering, unless a column specifies the ordering. For a column lead() and lag() do the same thing — just the ordering of the sort is different. What is the previous row for one is the next row for the other.

There are some small caveats. NULL values might be treated differently. And identical values result in an unstable ordering, so any matching value might be returned.

In general, lag() and lead() are used without desc, but that is just a convention to choose between the two functions.