Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Churned customers
Customer retention charts
Summary
9. Summary

## Instruction

Great! It's time to wrap things up. First, a quick review:

1. To count the number of churned customers (defined as people who haven't placed an order in the last 30 days), use:
SELECT COUNT(*)
FROM Customers
WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) > 30;

2. To count the number of churned customers in weekly registration cohorts, use:
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);

3. To calculate the percentage of churned customers in weekly registration cohorts, use:
SELECT
DATEPART(Year, RegistrationDate) AS Year,
DATEPART(Week, RegistrationDate) AS Week,
COUNT(CASE WHEN DATEDIFF(Day, LastOrderDate, GETDATE()) > 30 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS ChurnedPercentage
FROM Customers
GROUP BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate)
ORDER BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate);

4. To create a customer retention chart, use:
SELECT
DATEPART(Year, RegistrationDate) AS Year,
DATEPART(Week, RegistrationDate) AS Week,
COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 30 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS PercentActive30d,
COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 60 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS PercentActive60d,
COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, LastOrderDate) > 90 THEN CustomerID END) * 100.0 / COUNT(CustomerID) AS PercentActive90d
FROM Customers
GROUP BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate)
ORDER BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate);


Okay, time for a short quiz!

## Exercise

Click to continue.