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

Question

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

Answer 1

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