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.

This exercise is part of the course

Reporting in SQL

View Course

Exercise instructions

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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT
	-- Pull month and country_id
	____ AS month,
	____,
    -- Pull in current month views
    ____ AS month_views,
    -- Pull in last month views
    ____ AS previous_month_views,
    -- Calculate the percent change
    ____ AS perc_change
FROM ____
WHERE date <= '2018-05-31'
GROUP BY ____;

Edit and Run Code