Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Customer cohorts
14. Customer cohort report


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:

  DATE_PART('year', registration_date) AS registration_year,
  COUNT(*) AS registration_count
FROM customers cu
JOIN channels ch
  ON cu.channel_id =
GROUP BY DATE_PART('year', registration_date), channel_name
ORDER BY DATE_PART('year', registration_date);


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.

Stuck? Here's a hint!

Create the second column in the following way:

DATE_PART('week', registration_date) AS registration_week