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

Exercise

Month-over-month comparison

In order to compare months, you need to use one of the following window functions:

  • LAG(value, offset), which outputs a value from an offset number previous to to the current row in the report.
  • LEAD(value, offset), which outputs a value from a offset number after the current row in the report.

Your goal is to build a report that shows each country's month-over-month views. A few tips:

  • You will need to bucket dates into months. To do this, you can use the DATE_PART() function.
  • You can calculate the percent change using the following formula: (value)/(previous_value) - 1.
  • If no offset value is included in the LAG() or LEAD() functions, it will default to 1.

Since the table stops in the middle of June, the query is set up to only include data to the end of May.

Instructions

100 XP
  • From web_data, pull in country_id and use a DATE_PART() function to create month.
  • Create month_views that pulls the total views within the month.
  • Create previous_month_views that pulls the total views from last month for the given country.
  • Create the field perc_change that calculates the percent change of this month relative to last month for the given country, where a negative value represents a loss in views and a positive value represents growth.