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.). We can use the DATE_PART() function to create such reports:
SELECT
DATE_PART('year', registration_date) AS registration_year,
COUNT(customer_id) AS registration_count
FROM customers
GROUP BY DATE_PART('year', registration_date)
ORDER BY DATE_PART('year', registration_date);
Result:
| registration_year | registration_count |
|---|---|
| 2017 | 332 |
| 2018 | 409 |
| 2019 | 259 |
In our example, DATE_PART('year', registration_date) 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.
Create a report containing the 2017 monthly registration counts. Show the registration_month and registration_count columns. Order the results by month.
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 registration_date >= '2017-01-01' AND registration_date < '2018-01-01'
