CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
timy
timy

Reputation: 97

How can I use the LAG FUNCTION to show revenue this year vs last year in Snowflake with?

I would like to show the current revenue vs last's year's revenue in the same row per region per type. Example:For 2022-04-01, US, Type 1 --> REVENUE: 2456, REVENUE_LAST_YEAR: 4000

2021-04-01, US, Type 1 --> REVENUE: 4000, REVENUE_LAST_YEAR: 0

For some reason, the Lag formula in Snowflake is showing wrong values. Could someone please help ?

 WITH
indata(dt,region,type,revenue) AS (
          SELECT DATE '2021-04-01','US','Type 1',4000 UNION ALL SELECT DATE '2021-05-01','Europe','Type 2',5777
UNION ALL SELECT DATE '2021-06-01','US','Type 1',45433 UNION ALL SELECT DATE '2021-07-01','Europe','Type 2',8955
UNION ALL SELECT DATE '2021-08-01','US','Type 1',45777 UNION ALL SELECT DATE '2021-09-01','Asia','Type 1',7533
UNION ALL SELECT DATE '2021-10-01','US','Type 1',8866 UNION ALL SELECT DATE '2021-11-01','Asia','Type 2',5534
UNION ALL SELECT DATE '2021-12-01','US','Type 2',4000 UNION ALL SELECT DATE '2022-01-01','Asia','Type 1',7244
UNION ALL SELECT DATE '2022-02-01','US','Type 1',6678 UNION ALL SELECT DATE '2022-03-01','Asia','Type 1',5654
UNION ALL SELECT DATE '2022-04-01','US','Type 1',2456 UNION ALL SELECT DATE '2022-05-01','Asia','Type 1',4525
UNION ALL SELECT DATE '2022-06-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-07-01','Asia','Type 1',6654
UNION ALL SELECT DATE '2022-08-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-09-01','Asia','Type 2',5754
UNION ALL SELECT DATE '2022-10-01','US','Type 1',7744 UNION ALL SELECT DATE '2022-11-01','Asia','Type 2',5644
UNION ALL SELECT DATE '2022-12-01','Europe','Type 2',6775 UNION ALL SELECT DATE '2023-01-01','Asia','Type 2',6777
UNION ALL SELECT DATE '2023-02-01','Europe','Type 2',7755
)


SELECT indata.*, 
lag(REVENUE, 1, 0)  over (partition by region,type,revenue order by year(dt)) REVENUE_last_year


FROM indata

order by year(dt)

Upvotes: 2

Views: 330

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Partitioning by region, type and month-day:

SELECT indata.*, 
LAG(REVENUE, 1, 0)  over (partition by region,type, TO_VARCHAR(dt, 'mmdd') 
                          order by dt) AS REVENUE_last_year
FROM indata
ORDER BY dt;

Output:

enter image description here

Upvotes: 1

Related Questions