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()orLEAD()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 is a part of the course
“Reporting in SQL”
Exercise instructions
- From
web_data, pull incountry_idand use aDATE_PART()function to createmonth. - Create
month_viewsthat pulls the total views within the month. - Create
previous_month_viewsthat pulls the total views from last month for the given country. - Create the field
perc_changethat 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 ____;This exercise is part of the course
Reporting in SQL
Learn how to build your own SQL reports and dashboards, plus hone your data exploration, cleaning, and validation skills.
What is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.