End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer cohorts
Summary
14. Summary

Instruction

Perfect! It's time to wrap things up. What have we learned in this section?

  1. To count registrations during a given period of time, use:
    SELECT COUNT(CustomerID) AS ...
    FROM customers
    WHERE RegistrationDate >= ...
      AND RegistrationDate <  ...;
    
  2. To count registrations from the current period (e.g., the current year), use:
    SELECT COUNT(CustomerID) AS RegistrationCount
    FROM Customers
    WHERE RegistrationDate >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0);
    
  3. To show a report of registration counts for all years, use:
    SELECT
      DATEPART(year, RegistrationDate) AS RegistrationYear,
      COUNT(CustomerID) AS RegistrationCount
    FROM Customers
    GROUP BY DATEPART(year, RegistrationDate)
    ORDER BY DATEPART(year, RegistrationDate);
    
  4. A customer cohort is a group of customers that share a common characteristic over a certain time period. To show annual registration cohorts per channel, use:
    SELECT
      DATEPART(year, RegistrationDate) AS RegistrationYear,
      ChannelName,
      COUNT(*) As RegistrationCount
    FROM Customers Cu
    JOIN Channels Ch
      ON Cu.ChannelId = Ch.Id
    GROUP BY DATEPART(year, RegistrationDate), ChannelId, ChannelName
    ORDER BY DATEPART(year, RegistrationDate);
    

All right! How about a short quiz?

Exercise

Click Next exercise to continue.