Good! We now want to create a report which will count registration values in annual customer cohorts based on the channel. To that end, we can use the following query:
SELECT
DATE_PART('year', registration_date) AS registration_year,
channel_name,
COUNT(*) AS registration_count
FROM customers cu
JOIN channels ch
ON cu.channel_id = ch.id
GROUP BY DATE_PART('year', registration_date), channel_name
ORDER BY DATE_PART('year', registration_date);
Result:
| registration_year | channel_name | registration_count |
|---|---|---|
| 2016 | Organic Search | 48 |
| 2016 | Direct | 8 |
| 2016 | Referral | 4 |
| ... | ||
The customers table contains the channel ID for each user, but we join it with the channels table to get channel names for the report. Note that we only show channel names in the report, but we group by both the channel IDs and channel names. If two channels with different IDs had the same name (for whatever reason), this trick would prevent us from summing up their values improperly.
Create an extended version of the report shown in the explanation. Instead of annual customer cohorts per channel, show weekly customer cohorts per channel in each year.
Show the following columns: registration_year, registration_week, channel_name, and registration_count.
Order the results by year and week.
Create the second column in the following way:
DATE_PART('week', registration_date) AS registration_week
