Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Conversion rates
Time to first order
Conversion charts
17. Conversion rate report – exercise


Nice work! Time for an additional exercise.


Create a conversion chart for monthly registration cohorts from 2017 for each channel. Show the following columns:

  • month – The registration month.
  • channel_name – The registration channel.
  • registered_count – The number of users registered.
  • no_sale – The number of users who never made a purchase.
  • one_week – The number of users who made a purchase within 7 days.
  • after_one_week – The numbers of users who made a purchase after the first week.

Order the results by month.

Stuck? Here's a hint!

  1. Use DATE_PART('month', registration_date) to get the month.
  2. Join the customers table with the channels table to get channel names.
  3. Group by month and channel name.
  4. Use the following code to calculate the one_week column:
    COUNT(CASE WHEN first_order_date - registration_date < INTERVAL '7' day THEN customer_id END) AS one_week