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
DATE_PART('year', registration_date) AS registration_year,
DATE_PART('quarter', registration_date) AS registration_quarter,
COUNT(customer_id) AS registration_count
FROM customers
GROUP BY DATE_PART('year', registration_date), DATE_PART('quarter', registration_date)
ORDER BY DATE_PART('year', registration_date), DATE_PART('quarter', registration_date);
Result:
| registration_year | registration_quarter | registration_count |
|---|---|---|
| ... | ||
| 2016 | 4 | 23 |
| 2017 | 1 | 10 |
| 2017 | 2 | 8 |
| ... | ||
As you can see, we now use the DATE_PART() function twice: the first time with the registration_year column and the second time with the registration_quarter column. Note that we use both columns in three places: in the SELECT, GROUP BY, and ORDER BY clauses.
Find the registration count for each month in each year. Show the following columns: registration_year, registration_month, and registration_count. Order the results by year and month.
Use the code from the explanation. All you need to change is the quarter part in the second DATE_PART() instance and the corresponding column name.
