I have four grouping variables Month
, State
,County
, City
. In addition I have the metric column sales
which can be null I would like to calculate the percent change of sales per month for each City
.
My solution would have the same grouping but with the sales
column replaced by percent change for each month in calendar year 2019. Any help with a solution is appreciated.
Advertisement
Answer
You can use window functions:
x
select month, state, city, sales,
lag(sales) over (partition by state, city order by month) as prev_month,
(-1 + sales / lag(sales) over (partition by state, city order by month)) as change_ratio
from t;