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

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL

Introduction
Conversion rates
Time to first order
Conversion charts
17. Conversion rate report – exercise
Summary

Instruction

Nice work! Time for an additional exercise.

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