I was recently asked how to build a PostgreSQL query that calculated week-over-week business growth.

No problem! Just pair the `DATE_TRUNC`

function with a `GROUP BY`

clause to round times to weeks and aggregate new customer counts or sales revenue sums for each week:

This query is good for quick tallying but may not calculate the right results for everyone. Let's talk about why.

## Fixed week offsets

By default, `DATE_TRUNC('WEEK', time)`

rounds `time`

down to midnight on Monday:

But some businesses start their weeks on Sundays. In those cases, this article does a fantastic job of explaining how to apply a fixed one-day week offset. Here's the recap:

This `SELECT`

can be easily modified or generalized to calculate fixed week offsets for any number of days. Great! Here's how a two-day week offset looks when applied to the query from the introduction:

## Variable week offsets

Unfortunately, fixed week offsets can produce misleading results for unsuspecting analysts. It all depends on how the queried data gets bucketed into weeks.

For example, imagine that weeks start on Monday (week offset = 0). If we run a week-over-week query that examines a 28-day period starting on a Friday, then PostgreSQL will bucket the results over 5 weeks instead of 4 weeks! Worse, while 3 of those weeks will represent a full 7-day interval, the other 2 will represent partial weeks and thus show metrics that are artificially low. The oldest week will have data from only Friday to Sunday while the most recent week will represent only Monday to Thursday.

What if, instead, week-over-week metrics are calculated using a dynamic week offset that minimizes the number of partial weeks in the query's result? Assuming a contiguous window of time is being examined, the week offset would have to align the start of the week with either the window's start date or the day after the window's end date.

For a query retrieving only the last 28 complete days of data, the day after the window's end date would be today. This offset could be calculated as follows:

A cross join between this one-row result and the original query can then be used to aggregate metrics against a rolling week:

And look, only 4 weeks in the result, as desired! When this same query is run again tomorrow, there will still only be 4 weeks of data, with each week starting one day later than the dates shown above.

An important caveat to keep in mind when running this query is that, while it does return accurate results, the SQL query optimizer won't have access to table indexes to speed up the `GROUP BY`

aggregation. For large data sets, this can mean slow query times. If this is a problem, query from pre-calculated aggregate tables or in smaller chunks instead.

## Calculating week offsets

For me, the most intuitive approach for calculating week offsets was to calculate the interval difference between the desired start of the week and the actual start of the week. That's why this is the formula I used in the examples above:

But just as there's more than one way to skin a cat, there's more than one way to calculate a week offset. Instead of performing subtraction between two dates. we can take advantage of a builtin PostgreSQL feature to extract a date's day-of-week as an integer and then use that as the offset. A date's `ISODOW`

is 1 for Mondays and 7 for Sundays. To counter the 1-indexing, subtract one. Then, convert the integer to an interval, and voilà:

Here's the same strategy with a slightly more elegant conversion from integer to interval:

Note that there are two day-of-week datetime subfields in PostgreSQL: `DOW`

and `ISODOW`

. The `DOW`

is 0-indexed from Sundays (ending on a 6 for Saturday) while the `ISODOW`

is 1-indexed from Mondays (ending on a 7 for Sunday). Because PostgreSQL weeks start on Mondays, the `ISODOW`

subfield enables simpler math. That said, it's possible to use `DOW`

instead if you're willing to write fancy wraparound logic to correctly process Sundays.

## Some final notes

Something I don't demonstrate in this article but that I should mention is that week-over-week queries aren't required to map dates to the start of the week. It's not complicated to modify any of the above queries to map dates to the end of the week instead. The trick is to adjust how the week offsets are applied, not how they're calculated.

Additionally, there's a lot that can be said about grouping data by week while also being mindful of time zones, but I won't get into that today. Time is messy. I have plenty of stories about resolving bugs related to time. A thorough post on time zones will have to wait until another day.