Search code examples
sqlgoogle-bigquerysubtraction

Subtract the value of the most recent row with the value of the previous row (day -1)

I have a table with incremental value for each day. I'd like to subtract the value of the most recent row with the value of the previous row (day -1)

For example, this would be perfect : 

SUM(value) OVER (PARTITION BY item_name ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

However, I would need to apply a DIFF function instead of a SUM function.

Solution

  • Simply use lag():

    select val - lag(val) over (partition by item_name order by date)