Using the Lag window function in Grow (PostgreSQL)

I had a customer send in a ticket to get help creating a PostgreSQL query that would give him the delta of each row and the previous row in a specific column. He wanted the finished table to look like this:

After some digging around and consulting some smarter people around me, I came up with the following query using window functions (specifically the lag function) that would accomplish this for the customer.

SELECT *,
ABS("Deal Amount" - LAG("Deal Amount", 1, "Deal Amount") OVER (ORDER BY "Date")) AS "Moving Range"
FROM report

I'll break down what's happening in that query as simply as possible.

1. Since the customer is looking for the delta, he doesn't want any negative numbers, so we are wrapping the window function in an ABS() to get the absolute value.
2. First we grab the current row in "Deal Amount", from which we want to subtract the previous row.
3. Next, we subtract the previous row using the LAG() function. This function will return a value ("Deal Amount", that is offset x rows (in our case, 1) above the current row. If there is no such row, it returns the default, which is the current row.
4. To complete the window function, we must use an OVER clause (we also control the order in which the rows are processed by using ORDER BY).
5. Finally, we cast this information in a new column titled "Moving Range".

Here are a couple of screenshots of the SQL Transform and the resulting metric:

This type of function would come in handy in case your data source gives you an aggregate value instead of the added value each day. For example, Twitter's API will pull the total number of followers each day, but doesn't tell you how many new followers were added each day. Using the lag function, we can find this out.

Hope you find this helpful! And if you find any other great applications for this type of function, post it below!