CODEWITHSUNDEEP
sqldatesnowflake-cloud-data-platformwindow-functions
JVA
JVA

Reputation: 131

get % of increase or decrease when comparing one result to previous using dates

this is the query I have:

select 
    country,
    channel,
    month,
    sum(revenue)
from a 
group by 1,2,3

so, would it be possible to get the difference in % between the revenue of the current month compared to the previous?

Thanks in advance

Upvotes: 1

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can use lag() right along with aggregation:

select country, channel, month,
       sum(revenue) as revenue,
       (-1 + sum(revenue) / lag(sum(revenue)) over (partition by country, channel order by month)
       ) as change_ratio
from a
group by 1, 2, 3

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

here is one way :

select * 
    ,((revenue/ LAG(revenue,1,0) over (country,channel order by month)) -1)* 100.0 perc
from (
select 
    country,
    channel,
    month,
    sum(revenue) revenue
from a 
group by 1,2,3
) t

Upvotes: 0

Related Questions