Jamie Edgecombe Jamie Edgecombe - 4 months ago 8
SQL Question

Calculate month on month change in Oracle SQL

I have a data set that records revenue by member, by month, by product. I can sum the revenue by member like so:

select sum(revenue), member, month from member_revenue group by member, month


I want to calculate the month on month change is revenue by member, ideally without having to call the data in the table for a second time.

Answer

Just use lag():

select member, month, sum(revenue) as this_month_revenue,
       lag(sum(revenue)) over (partition by member order by month) as last_month_revenue
from member_revenue
group by member, month;

Note: This assumes that all months have revenue. LAG() gets the revenue from the preceding row, even if it is not the preceding month.