Let's first define what we mean by a "churned customer." In our business, we'll define a churned customer as a customer that hasn't placed an order in more than 30 days. This definition will be a starting point – we'll use other criteria in some of the other examples too. In your own business, you can use criteria that fit your business better.
The simplest question related to customer churn is:
As of today, how many churned customers are there in total?
Here's a query that will provide the answer:
SELECT COUNT(*) AS churned_customers FROM customers WHERE CURRENT_DATE - last_order_date > INTERVAL '30' day;
In the WHERE clause, we used the difference between last_order_date and today. To get the current date, we used the CURRENT_DATE function.
Out of customers registered in 2017, find the number of churned customers. Define a churned customer as one who hasn't placed an order in more than 60 days. Show the count in a column named churned_customers.
Does this number seem large?
Add the following condition in the WHERE clause:
registration_date >= '2017-01-01' AND registration_date < '2018-01-01'
