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. Any help is appreciated!

Thanks in advance.

CodePudding user response：

SELECT 
    date,
    fruits_sold,
    vegetable_sold,
    lag(vegetable_sold) over (order by date) as prev_vegetable_sold,
    lag(fruits_sold) over (order by date) as prev_fruits_sold,
    ((vegetable_sold/prev_vegetable_sold)*100)-100 as change_in_veg_count,
    ((fruits_sold/prev_fruits_sold)*100)-100 as change_in_fruit_count
    
FROM VALUES
    ('2022-01-01'::date, 18, 11)
    ,('2022-01-02'::date, 20, 16)
    ,('2022-01-03'::date, 25, 40)
    ,('2022-01-04'::date, 12, 14)
    ,('2022-01-05'::date, 18, 6)
    d(date, fruits_sold, vegetable_sold);

gives:

which shows how the LAG is used to get prior value. And if you really don't need to reuse the value for other things, you can bundle it into a single line as Lukasz has shown:

SELECT 
    date,
    ((vegetable_sold/lag(vegetable_sold) over (order by date))*100)-100 change_in_veg_count,
    ((fruits_sold/lag(fruits_sold) over (order by date))*100)-100 change_in_fruit_count
FROM VALUES
    ('2022-01-01'::date, 18, 11)
    ,('2022-01-02'::date, 20, 16)
    ,('2022-01-03'::date, 25, 40)
    ,('2022-01-04'::date, 12, 14)
    ,('2022-01-05'::date, 18, 6)
    d(date, fruits_sold, vegetable_sold);

CodePudding user response：

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