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:
DATEPART(year, RegistrationDate) AS RegistrationYear,
COUNT(CustomerID) AS RegistrationCount
GROUP BY DATEPART(year, RegistrationDate)
ORDER BY DATEPART(year, RegistrationDate);
DATEPART(time unit, date) function takes two parameters: a time unit (such as
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.