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 to continue.