Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL Server

Introduction
Customer registration
Customer cohorts
Summary

Instruction

Good job! We can also create a more advanced version of this report by grouping all rows using multiple date parts. For instance, suppose we need to find the registration count for each quarter of each year. This will allow us to compare values for the same quarter across different years.

SELECT
  DATEPART(year, RegistrationDate) AS RegistrationYear,
  DATEPART(quarter, RegistrationDate) AS RegistrationQuarter,
  COUNT(CustomerID) AS RegistrationCount
FROM Customers
GROUP BY DATEPART(year, RegistrationDate), DATEPART(quarter, RegistrationDate)
ORDER BY DATEPART(year, RegistrationDate), DATEPART(quarter, RegistrationDate);

Result:

RegistrationYear RegistrationQuarter RegistrationCount
...
2016 4 23
2017 1 10
2017 2 8
...

As you can see, we now use the DATEPART() function twice: the first time with the RegistrationYear column and the second time with the RegistrationQuarter column. Note that we use both columns in three places: in the SELECT, GROUP BY, and ORDER BY clauses.

Exercise

Find the registration count for each month in each year. Show the following columns: RegistrationYear, RegistrationMonth, and RegistrationCount. Order the results by year and month.

Stuck? Here's a hint!

Use the code from the explanation. All you need to change is the quarter part in the second DATEPART() instance and the corresponding column name.