Get Started

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 is a 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

This exercise is part of the course

Reporting in SQL

IntermediateSkill Level
4.8+
20 reviews

Learn how to build your own SQL reports and dashboards, plus hone your data exploration, cleaning, and validation skills.

Start Course for Free

The value of reporting really shows when presenting not-so-obvious insights through complex calculations. In this chapter, you will learn how to build more complicated fields by leveraging window functions and layered calculations. You will gain hands-on experience building two advanced calculations in particular: the percent of a total calculation and the performance index calculation.

Exercise 1: Building complex calculationsExercise 2: Testing out window functionsExercise 3: Average total country medals by regionExercise 4: Most decorated athlete per regionExercise 5: Comparing groupsExercise 6: Volume vs efficiency metricsExercise 7: Percent of gdp per countryExercise 8: GDP per capita performance indexExercise 9: Comparing datesExercise 10: Month-over-month comparison
Exercise 11: Week-over-week comparisonExercise 12: Report 4: Tallest athletes and % GDP by regionExercise 13: Course summary

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free