Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer cohorts
Summary
16. 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(customer_id) AS ...
    FROM customers
    WHERE registration_date >= ...
      AND registration_date <  ...;
    
  2. To count registrations from the current period (e.g., the current year), use:
    SELECT COUNT(customer_id) AS registration_count
    FROM customers
    WHERE registration_date >= DATE_TRUNC('year', CURRENT_TIMESTAMP);
    
  3. To show a report of registration counts for all years, use:
    SELECT
      DATE_PART('year', registration_date) AS registration_year,
      COUNT(customer_id) AS registration_count
    FROM customers
    GROUP BY DATE_PART('year', registration_date)
    ORDER BY DATE_PART('year', registration_date);
    
  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
      DATE_PART('year', registration_date) AS registration_year,
      channel_name,
      COUNT(*) AS registration_count
    FROM customers cu
    JOIN channels ch
      ON cu.channel_id = ch.id
    GROUP BY DATE_PART('year', registration_date), channel_name
    ORDER BY DATE_PART('year', registration_date);
    

All right! How about a short quiz?

Exercise

Click Next exercise to continue.