End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer activity
Finding good customers
Summary
15. Recap

Instruction

Perfect! It's time to wrap things up.

  1. To find the number of customers active within the last 30 days in weekly registration cohorts, use:
    SELECT 
      DATEPART(Year, RegistrationDate) AS Year, 
      DATEPART(Week, RegistrationDate) AS Week, 
      COUNT(*) AS ActiveCustomers
    FROM Customers
    WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) < 30
    GROUP BY
      DATEPART(Year, RegistrationDate), 
      DATEPART(Week, RegistrationDate)
    ORDER BY
      DATEPART(Year, RegistrationDate), 
      DATEPART(Week, RegistrationDate);
    
  2. To find the average order value in weekly registration cohorts, use:
    SELECT 
      DATEPART(Year, RegistrationDate) AS Year, 
      DATEPART(Week, RegistrationDate) AS Week, 
      AVG(TotalAmount) AS AverageOrderValue
    FROM Customers C
    JOIN Orders O
      ON C.CustomerID = O.CustomerID
    GROUP BY
      DATEPART(Year, RegistrationDate), 
      DATEPART(Week, RegistrationDate)
    ORDER BY
      DATEPART(Year, RegistrationDate), 
      DATEPART(Week, RegistrationDate);
    
  3. To find the number of "good customers" in weekly registration cohorts, use:
    WITH GoodCustomers AS (
    SELECT
      C.CustomerID,
      RegistrationDate,
      AVG(TotalAmount) AS AvgOrderValue
    FROM Customers C
    JOIN Orders O
      ON C.CustomerID = O.CustomerID
    GROUP BY C.CustomerID, RegistrationDate
    HAVING AVG(TotalAmount) > 1636
    )
    SELECT 
      DATEPART(Year, RegistrationDate) AS Year, 
      DATEPART(Week, RegistrationDate) AS Week,
      COUNT(*) AS GoodCustomers
    FROM GoodCustomers
    GROUP BY
      DATEPART(Year, RegistrationDate), 
      DATEPART(Week, RegistrationDate)
    ORDER BY
      DATEPART(Year, RegistrationDate), 
      DATEPART(Week, RegistrationDate);
    

How about a quick quiz before we move on to the next part?

Exercise

Click Next exercise to continue.