Lesson 11 of 14 · 26 min

Period-over-period: month-over-month and year-over-year

By the end of this lesson you can build month-over-month and year-over-year comparisons with date_trunc and LAG (or a date join), compute growth percentages safely, and avoid the filter-before-LAG and incomplete-period traps.

Period-over-period analysis has three steps:

Bucket rows into periods with date_trunc('month', ts) (or 'week', 'quarter', 'year'). It returns the start of the period, which sorts correctly and joins cleanly. Avoid to_char(ts, 'Mon YYYY') until the very end: text months sort alphabetically.
Aggregate one row per period.
Compare each period with an earlier one: the previous row with LAG(x) (month-over-month), or twelve months back with LAG(x, 12) or a join on month - interval '1 year' (year-over-year).

Included with a Formula Quest membership

This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.

View Formula Quest memberships Back to Intermediate and Advanced Analytics

Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons