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 Server

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
  • ActiveAfter14Days
  • ActiveAfter30Days
  • ActiveAfter60Days

Sort the results by week.

Stuck? Here's a hint!

Use the DATEDIFF() function to find the time difference between the RegistrationDate and the LastOrderDate.

Calculate the ActiveAfter14Days column in the following way:

COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 14 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS ActiveAfter14Days