Week-over-week comparison
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.
Syntax for a rolling average is AVG(value) OVER (PARTITION BY field ORDER BY field ROWS BETWEEN N PRECEDING AND CURRENT ROW), where N is the number of rows to look back when doing the calculation. Remember that CURRENT ROW counts as a row.
This exercise is part of the course
Reporting in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Pull in date and daily_views
____,
____ AS daily_views,
-- Calculate the rolling 7 day average
____ AS weekly_avg
FROM ____
GROUP BY ____;