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:

=> SELECT DATE_TRUNC('WEEK', created_at) AS week,
          COUNT(*)
     FROM customers
 GROUP BY 1
 ORDER BY 1 DESC;

        week         | count 
---------------------+-------
 2021-04-12 00:00:00 |    75
 2021-04-05 00:00:00 |    50
 2021-03-29 00:00:00 |    25
 .
 .
 .
(9 lignes)
This query demonstrates how to count the number of new customers 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:

=> SELECT DATE_TRUNC('WEEK', '2021-04-16'::date) AS week;

          week          
------------------------
 2021-04-12 00:00:00+00
(1 ligne)
Friday (2021-04-16) truncated to the nearest week is Monday at midnight (2021-04-12).

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:

=> SELECT DATE_TRUNC('WEEK', '2021-04-16'::date + 1)::date - 1 AS week;

    week    
------------
 2021-04-11
(1 ligne)
With two shifts, Friday (2021-04-16) truncated to the nearest week is Sunday (2021-04-11).

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:

=> SELECT DATE_TRUNC('WEEK', created_at::date + 2)::date - 2 AS week,
          COUNT(*)
     FROM customers
 GROUP BY 1
 ORDER BY 1 DESC;

        week         | count 
---------------------+-------
 2021-04-10 00:00:00 |    75
 2021-04-03 00:00:00 |    50
 2021-03-27 00:00:00 |    25
 .
 .
 .
(9 lignes)
This query demonstrates how to apply a fixed week offset so that weeks start on Saturdays.

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.

A SQL query that groups 28 days of data by week can produce 5 weeks of data.

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:

=> SELECT CURRENT_DATE - DATE_TRUNC('WEEK', CURRENT_DATE) AS offset;

 offset 
--------
 4 days
(1 ligne)
The week offset is the desired start of the week minus the actual start of the week.

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

=> SELECT CURRENT_DATE;

 current_date 
--------------
 2021-04-16
(1 ligne)

=>  SELECT DATE_TRUNC('WEEK', created_at::date - monday.offset)::date + monday.offset AS week,
           COUNT(*)
      FROM customers
CROSS JOIN (SELECT CURRENT_DATE - DATE_TRUNC('WEEK', CURRENT_DATE) AS offset) monday
     WHERE created_at BETWEEN CURRENT_DATE - 28 AND CURRENT_DATE
  GROUP BY 1
  ORDER BY 1 DESC;
  
        week         | count 
---------------------+-------
 2021-04-09 00:00:00 |    75
 2021-04-02 00:00:00 |    50
 2021-03-26 00:00:00 |    25
 2021-03-19 00:00:00 |    10
(4 lignes)
This query demonstrates how to calculate and apply a variable week offset.

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:

=> SELECT '2021-04-16'::DATE - DATE_TRUNC('WEEK', '2021-04-16'::DATE) AS offset;

 offset 
--------
 4 days
(1 ligne)
The week offset is the interval between desired start of the week and actual start of the week.

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à:

=> SELECT CONCAT(EXTRACT(ISODOW FROM '2021-04-16'::DATE) - 1, ' days')::INTERVAL AS offset;

 offset 
--------
 4 days
(1 ligne)
This week offset calculation uses the builtin PostgreSQL day-of-week subfield.

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

=> SELECT MAKE_INTERVAL(days => EXTRACT(ISODOW FROM '2021-04-16'::DATE)::INTEGER - 1) AS offset;

 offset 
--------
 4 days
(1 ligne)
This week offset calculation also uses the builtin PostgreSQL day-of-week subfield.

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.