Get startedGet started for free

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

View Course

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 ____;
Edit and Run Code