How to Group Data by Week in SQL Server

This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server.

In this course, we showed you how you can analyze the customer lifecycle (customer acquisition, conversion, activity, retention, and churn) with SQL. We discussed customer registration cohorts, or groups of customers who registered during the same period (e.g. same week, same month). Analyzing customer registration cohorts lets you see registration trends and relate registration cohorts with marketing campaigns.

In practice, the most convenient registration cohorts are weekly registration cohorts. Yearly, quarterly, or monthly registration cohorts are too imprecise for a meaningful analysis. On the other hand, daily or hourly registration cohorts are too specific. Weekly registration cohorts are usually just right.

In this article, we'll look at different ways to group data by week in SQL Server.

How Do You Group Data by Week in SQL Server?

SQL Server provides a function called DATEPART(), which returns a specified part (year, quarter, month, week, hour, minute, etc.) of a specified date.

To group customers who registered in 2018 by the week, you can use this query:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

As you can see, the DATEPART() function takes two arguments: datepart (i.e., the identifier of the desired part) and the date from which you extract the part.

The DATEPART() function has two datepart arguments that will return week data:

• week (also abbreviated wk, ww).
• iso_week (also abbreviated isowk, isoww).

We'll explain the differences between these two types shortly. But first, we need to discuss another setting.

The DATEFIRST Setting

The DATEFIRST setting tells SQL Server which weekday it should consider as the first day of the week. DATEFIRST can be one of the following values:

The DATEFIRST setting depends on SQL Server's language version. The default value for U.S. English is 7 (i.e. Sunday).

You can change the value of DATEFIRST like this:

SET DATEFIRST 1

DATEFIRST is a session setting, which means you can change it without impacting other users.

You can find the current value of the DATEFIRST setting with the @@DATEFIRST function:

SELECT @@DATEFIRST;

Result:

7

The first day of the week is 7, i.e. Sunday.

Using DATEPART() with week

Now that we know the DATEFIRST setting, let's take a look at how DATEPART() with week works.

The behavior of DATEPART() with week depends on what day DATEFIRST has set as the first day of the week. The weeks of each year are numbered separately. Week 1 is the week that contains (but does not necessarily start with) January 1.

Let's look at an example. The image shows three different calendars for January 2019; each one specifies a different weekday as the first day of the week:

• The first calendar has Sunday as the first day of the week, as typically used in the United States.
• The second calendar shows Monday as the first day of the week, as typically used in Europe.
• The third calendar uses Thursday as the first day of the week. (Just as an example of a non-typical week.)

DATEPART() with week uses the week numbering shown in the image:

• If Sunday is the first day of the week (DATEFIRST = 7), Week 2 starts on Sunday 6 January and ends on Saturday 12 January.
• If Monday is the first day of the week (DATEFIRST = 1), Week 2 starts on Monday 7 January and ends on Sunday 13 January.
• If Thursday is the first day of the week (DATEFIRST = 4), Week 2 starts on Thursday 3 January and ends on Wednesday 9 January.

A typical use of DATEPART() with week is to group data by week via the GROUP BY clause. We also use it in the SELECT clause to display the week number. Have a look at the query below and its result:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

If you find week numbers unreadable, look at the article on How to Get the First Day of the Week.

Notice that for DATEPART() with week, the week where the year ends and the next begins is often split. In other words, the last few days of December are placed in week 52/53 of the preceding year, while the first days of January are in week 1 of the new year.

Using DATEPART() with iso_week

DATEPART() has another datepart argument for weeks: iso_week. This argument handles weeks according to ISO 8601, which is an international standard covering the exchange of date and time data.

In the ISO 8601 standard, weeks begin on Monday. Week 1 of a year is the week in which the first Thursday of that year occurs. This style of week numbering is typically used in European countries.

For example, week 1 of 2017 was Monday, 2 January to Sunday, 8 January. It's important to note that if 1 January is on a Friday, Saturday, or Sunday, it is considered as part of week 52/53 of the previous year.

Here's the calendar for January 2017:

Once again, you typically use DATEPART() with iso_week in GROUP BY and SELECT:

SELECT
  DATEPART(iso_week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(iso_week, RegistrationDate)
ORDER BY DATEPART(iso_week, RegistrationDate);

If you find week numbers unreadable, look at the article on How to Get the First Day of the Week.