Instruction
Good job! Let's move on. We now want to find out how customer acquisition has changed over time. This will help us understand if we're currently attracting more users (or not).
To that end, we'll compare the registration count values for various periods (year to year, month to month, etc.). In Microsoft SQL Server, we can use the DATEPART() function to create such reports:
SELECT DATEPART(year, RegistrationDate) AS RegistrationYear, COUNT(CustomerID) AS RegistrationCount FROM Customers GROUP BY DATEPART(year, RegistrationDate) ORDER BY DATEPART(year, RegistrationDate);
Result:
| RegistrationYear | RegistrationCount |
|---|---|
| 2017 | 332 |
| 2018 | 409 |
| 2019 | 259 |
The DATEPART(time unit, date) function takes two parameters: a time unit (such as year, quarter, month, etc.) and a date/time value. As the name suggests, the function returns the specific part from the given date. In our example, DATEPART(year, OrderDate) returns the year of user registration (2016, 2017, 2018, ...).
Note that we also added an ORDER BY clause to make sure the registration count values are shown in chronological order.
Exercise
Create a report containing the 2017 monthly registration counts. Show the RegistrationMonth and RegistrationCount columns. Order the results by month.
Stuck? Here's a hint!
Compared to the code in the explanation, you'll have to add a WHERE clause and filter for users registered in 2017. If you don't, the registration counts across all years would be summed for matching months. In other words, the September value would show a sum of registration counts from all Septembers we have data about – September 2017, September 2018, etc.
Add the following WHERE clause:
WHERE RegistrationDate >= '20170101' AND RegistrationDate < '20180101'
