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 DATEPART(year, RegistrationDate) AS RegistrationYear, ChannelName, COUNT(*) AS RegistrationCount FROM Customers Cu JOIN Channels Ch ON Cu.ChannelId = Ch.Id GROUP BY DATEPART(year, RegistrationDate), ChannelId, ChannelName ORDER BY DATEPART(year, RegistrationDate);
Result:
| RegistrationYear | ChannelName | RegistrationCount |
|---|---|---|
| 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: RegistrationYear, RegistrationWeek, ChannelName, and RegistrationCount.
Order the results by year and week.
Create the second column in the following way:
DATEPART(week, RegistrationDate) As RegistrationWeek
