Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Churned customers
Customer retention charts
Summary
9. Summary

Instruction

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!

Exercise

Click Next exercise to continue.