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

Final Quiz
5. Question 4

Instruction

Great! In the last question, we'll ask you to create a customer retention chart.

Exercise

Create a customer retention chart for weekly signup cohorts from the third quarter of 2018. For each cohort, find the percentage of customers still active after 14 days, 30 days, and 60 days. Show the following columns:

  • week
  • active_after_14_days
  • active_after_30_days
  • active_after_60_days

Sort the results by week.

Stuck? Here's a hint!

Use an interval to find the time difference between the registration_date and the last_order_date.

Calculate the active_after_14_days column in the following way:

COUNT(CASE WHEN last_order_date - registration_date > INTERVAL '14' day THEN customer_id END) * 100.0 / COUNT(customer_id) AS active_after_14_days