End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Final Quiz
3. Question 2

Instruction

Nice work! Let's move on to customer conversion.

Exercise

Create a conversion chart showing the 2017 Q1 monthly registration cohorts and the number of conversions (i.e., the number of customers who made their first purchase) in the first week, the first two weeks, and more than two weeks after registering. Show the following columns:

  • month – the month of registration.
  • no_sale – the number of customers who never placed an order.
  • first_week – the number of customers who placed their first order within the first week.
  • second_week – the number of customers who placed their first order within the second week.
  • after_second_week – the number of customers who placed their first order after more than two weeks.

Order the results by year and month.

Stuck? Here's a hint!

Use the first_order_date column to create this report.

You can find the number of customers who made a purchase within the first week with:

COUNT(CASE WHEN first_order_date - registration_date < INTERVAL '7' day THEN customer_id END) AS first_week

Use this pattern in the other two similar columns.