End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer cohorts
Summary

Instruction

Great! You may have noticed that displaying the week number is not very readable. After all, when does week 22 start? Is it in April, in May, or in June? If you'd like to see the week label as a date, the quick and dirty trick to solve this problem is to display the minimum registration date in a week.

SELECT
  DATE_PART('year', registration_date) AS registration_year,
  DATE_PART('week', registration_date) AS registration_week,
  MIN(registration_date) AS week_label,
  COUNT(customer_id) AS registration_count
FROM customers
GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);

This query will show the year and week number, and the earliest date in this week. This solution is not perfect: if there are no registrations on Monday, then you'll see Tuesday's date as the week label for this week.

Many databases have functions to display the first day of the week. For example, in PostgreSQL you can use DATE_TRUNC() with the week parameter (DATE_TRUNC('week', date)) to display the first day of the week. Check out the documentation of your database to see if it offers a function which computes the first day of the week.

Exercise

The template shows the solution for the previous exercise. Add a new column week_label displaying the minimum registration date in each week. Is the result more readable now?

Stuck? Here's a hint!

Just add a new column in the query:

MIN(registration_date) AS week_label