# find month percentage change

Thu Apr 21 2022 05:37:08 GMT+0000 (Coordinated Universal Time)

Saved by @Bambibo9799

```with extractedalready as(
select extract(month from order_date) as mm,
round(sum(sales::numeric)) as current_rev,
lag(round(sum(sales::numeric),0))over(order by extract(month from order_date)) as pre_rev
from orders_table as ot
where extract(year from order_date)= 2017
group by mm
)
select *, round(current_rev/pre_rev-1,2)*100 as percentagechange

-- Pull month and country_id
DATE_PART('month', date) AS month,
country_id,
-- Pull in current month views
SUM(views) AS month_views,
-- Pull in last month views
LAG(SUM(views)) OVER (PARTITION BY country_id ORDER BY DATE_PART('month', date)) AS previous_month_views,
-- Calculate the percent change
SUM(views) / LAG(SUM(views)) OVER (PARTITION BY country_id ORDER BY DATE_PART('month', date)) - 1 AS perc_change
FROM web_data
WHERE date <= '2018-05-31'
GROUP BY month, country_id;

In the previous exercise, you leveraged the set window of a month to calculate month-over-month changes. But sometimes, you may want to calculate a different time period, such as comparing last 7 days to the previous 7 days. To calculate a value from the last 7 days, you will need to set up a rolling calculation.

In this exercise, you will take the rolling 7 day average of views for each date and compare it to the previous 7 day average for views. This gives a clear week-over-week comparison for every single day.

SELECT
-- Pull in date and daily_views
date,
SUM(views) AS daily_views,
-- Calculate the rolling 7 day average
AVG(SUM(views)) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM web_data
GROUP BY date;

SELECT
-- Pull in date and weekly_avg
date,
weekly_avg,
-- Output the value of weekly_avg from 7 days prior
LAG(weekly_avg,7) OVER (ORDER BY date) AS weekly_avg_previous,
-- Calculate percent change vs previous period
weekly_avg / LAG(weekly_avg,7) OVER (ORDER BY date) - 1 as perc_change
FROM
(SELECT
-- Pull in date and daily_views
date,
SUM(views) AS daily_views,
-- Calculate the rolling 7 day average
AVG(SUM(views)) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM web_data
-- Alias as subquery
GROUP BY date) AS subquery
-- Order by date in descending order
ORDER BY date DESC;```
content_copyCOPY