Perform Math operations on previous and current row value in Snowflake

Question

I have a requirement wherein I need to perform few mathematical calculations on the previous and current value of a column in Snowflake.

For eg:

My Snowflake table has data like below

Here CHANGE_IN_FRUIT_COUNT and CHANGE_IN_VEG_COUNT fields are calculated as below

((current_val/previous_val)*100)-100

I would like to implement the same logic in my table as well.

Answer 1

Using LAG:

SELECT DATE, 
   (FRUITS_SOLD / LAG(FRUITS_SOLD) 
                      OVER(ORDER BY DATE))*100-100 AS CHANGE_IN_FRUIT_COUNT.
   (VEGETABLE_SOLD / LAG(VEGETABLE_SOLD ) 
                      OVER(ORDER BY DATE))*100-100 AS CHANGE_IN_VEG_COUNT
FROM tab
ORDER BY DATE;

db<>fiddle demo