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 CURRENT_DATE - last_order_date > INTERVAL '30' day;
   

2. To count the number of churned customers in weekly registration cohorts, use:

   SELECT
     DATE_PART('year', registration_date) AS year,
     DATE_PART('week', registration_date) AS week,
     COUNT(*) AS churned_customers
   FROM customers
   WHERE CURRENT_DATE - last_order_date > INTERVAL '30' day
   GROUP BY
     DATE_PART('year', registration_date),
     DATE_PART('week', registration_date)
   ORDER BY
     DATE_PART('year', registration_date),
     DATE_PART('week', registration_date);
   

3. To calculate the percentage of churned customers in weekly registration cohorts, use:

   SELECT
     DATE_PART('year', registration_date) AS year,
     DATE_PART('week', registration_date) AS week,
     COUNT(CASE
       WHEN CURRENT_DATE - last_order_date > INTERVAL '30' day
       THEN customer_id
     END) * 100.0 / COUNT(customer_id) AS churned_percentage
   FROM customers
   GROUP BY
     DATE_PART('year', registration_date),
     DATE_PART('week', registration_date)
   ORDER BY
     DATE_PART('year', registration_date),
     DATE_PART('week', registration_date);
   

4. To create a customer retention chart, use:

   SELECT
     DATE_PART('year', registration_date) AS year,
     DATE_PART('week', registration_date) AS week,
     COUNT(CASE
       WHEN last_order_date - registration_date > INTERVAL '30' day
       THEN customer_id
     END) * 100.0 / COUNT(customer_id) AS percent_active_30d,
     COUNT(CASE
       WHEN last_order_date - registration_date > INTERVAL '60' day
       THEN customer_id
     END) * 100.0 / COUNT(customer_id) AS percent_active_60d,
     COUNT(CASE
       WHEN last_order_date - registration_date > INTERVAL '90' day
       THEN customer_id
     END) * 100.0 / COUNT(customer_id) AS percent_active_90d
   FROM customers
   GROUP BY
     DATE_PART('year', registration_date),
     DATE_PART('week', registration_date)
   ORDER BY
     DATE_PART('year', registration_date),
     DATE_PART('week', registration_date);
   

Okay, time for a short quiz!