Lag Function in Snowflake

user_18422938 0
Viewed 109

I have a requirement as below.

PRODUCT_ID SHIPMENT_YEAR SHIPMENT RETURN_QTY
1 2,016 140,464 2449
1 2,017 358,210 7433
1 2,018 14,299 798
1 2,019 12 1
1 2,020 11 0
1 2,021 15 0
1 2,022 1 0

The expected output is as below.

PRODUCT_ID SHIPMENT_YEAR SHIPMENT (C) RETURN_QTY (D) Effective Return Qty (E) Formula
1 2,016 140,464 2,449 138,015 C3 - D3
1 2,017 358,210 7,433 488,792 E3 + C4 - D4
1 2,018 14,299 798 502,293 E4 + C5 - D5
1 2,019 12 1 502,304 E5 + C6 - D6
1 2,020 11 0 502,315 E6 + C7 - D7
1 2,021 15 0 502,330 E7 + C8 - D8
1 2,021 1 0 502,331 E8 + C9 - D9

The column formula is to show how column E is derived.

Please help me to get above output using Snowflake query

snowflake-cloud-data-platform
1 Answers

You cannot use LAG, but can use a WINDOW FUNCTION on SUM, which allows you to accumulate a sum over the rows. Which is what you sre trying todo.

SELECT 
    product_id,
    shipment_year,
    shipment,
    return_qty,
    SUM( shipment - return_qty ) OVER (ORDER BY shipment_year) AS big_sum
FROM VALUES
    (1,2016,140464,2449),
    (1,2017,358210,7433),
    (1,2018,14299,798),
    (1,2019,12,1),
    (1,2020,11,0)
    t(product_id, shipment_year, shipment, return_qty);
PRODUCT_ID SHIPMENT_YEAR SHIPMENT RETURN_QTY BIG_SUM
1 2,016 140,464 2,449 138,015
1 2,017 358,210 7,433 488,792
1 2,018 14,299 798 502,293
1 2,019 12 1 502,304
1 2,020 11 0 502,315
user_439920
Related