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

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.