1. Learn
    2. /
  2. Courses
    3. /
  3. Reporting in SQL

Exercise

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.

Instructions 1/3

undefined XP
    1
    2
    3
  • Show daily_views and weekly_avg by date, where weekly_avg is the rolling 7 day average of views.