End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Churned customers
3. Churned customers in weekly cohorts
Customer retention charts
Summary

Instruction

Perfect! We know how to count the total number of churned customers. Next, let's see how we can split that count into weekly registration cohorts. Take a look:

SELECT
  DATEPART(Year, RegistrationDate) AS Year,
  DATEPART(Week, RegistrationDate) AS Week,
  COUNT(*) As ChurnedCustomers
FROM Customers
WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) > 30
GROUP BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate);

As usual, we used the DATEPART() function to extract the week and year of users' registrations. This information is used in the GROUP BY clause to group customers into weekly signup cohorts.

Exercise

Find the number of churned customers in monthly registration cohorts from 2017. In this exercise, churned customers are those who haven't placed an order in more than 45 days. Show the following columns: Month and ChurnedCustomers. Order the results by month.

Stuck? Here's a hint!

Remember to use the WHERE clause to filter customers based on RegistrationDate.