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!