Perfect! The last report we'll create will be a customer retention chart. For each weekly registration cohort, we want to see the percentage of customers still active 30, 60, and 90 days after the registration date. Take a look:

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);

Result:

By looking at a single row, we can analyze a given signup cohort and see what percentage of customers were still active after one, two, or three months. Customer retention figures will inevitably decrease over time. Comparing these values can help us determine if our customers are leaving us too quickly.

As you can see, we used the COUNT(CASE WHEN ...) construction three times, each time with a slightly different condition. Because of that, we can include multiple metrics in a single query. Other than this, the query used all the standard features we've already gotten to know.