Identifying Churned Customers

The Problem

The retention team wants to find customers who were previously active but have not placed an order in the last 90 days. These are not new signups who never bought anything -- they are people who demonstrated purchasing intent and then went silent.

This is the foundation of churn analysis. Identifying these customers early lets you trigger win-back campaigns, discount offers, or account manager outreach before the customer is fully lost.

The Pattern: Aggregate Filtering with HAVING

This question combines several concepts:

A JOIN between customers and orders to get purchase history
Aggregate functions (COUNT, SUM, MAX) to summarize each customer's behavior
HAVING to filter groups based on computed conditions (last order date threshold)

The subtlety is distinguishing between:

Customers who never ordered (should be excluded -- they are not "churned," they never activated)
Customers whose most recent order is within 90 days (active -- exclude)
Customers whose most recent order is older than 90 days (churned -- include)

Using an INNER JOIN on orders naturally handles the first case. The HAVING clause on MAX(order_date) handles the rest.

This pattern applies wherever you need to classify entities based on temporal behavior of related records:

SaaS accounts with no logins in 30 days but prior activity
Library patrons who haven't borrowed a book in 6 months
Suppliers who haven't fulfilled an order this quarter
Students who stopped submitting assignments mid-semester
Patients who missed follow-up appointments after an initial visit
Mobile app users who haven't opened the app since an update

The HAVING clause is often overlooked in favor of subqueries, but for this class of problem -- "filter groups by an aggregate condition" -- it is the most direct tool.

The Setup

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    signup_date DATE
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO customers (id, name, signup_date) VALUES
(1, 'Alice', '2023-01-15'),
(2, 'Bob', '2023-03-20'),
(3, 'Charlie', '2023-06-10'),
(4, 'Diana', '2023-09-01'),
(5, 'Eve', '2024-01-05');

INSERT INTO orders (id, customer_id, order_date, amount) VALUES
(1, 1, '2023-06-01', 120.00),
(2, 1, '2024-03-15', 85.00),
(3, 2, '2023-05-10', 200.00),
(4, 2, '2023-08-22', 150.00),
(5, 3, '2023-09-30', 95.00),
(6, 3, '2024-03-20', 110.00),
(7, 4, '2023-10-15', 175.00),
(8, 4, '2023-11-20', 60.00),
(9, 5, '2024-02-28', 300.00),
(10, 5, '2024-03-25', 250.00);

Using a reference date of 2024-03-28, Alice, Charlie, and Eve are active (ordered within 90 days). Bob and Diana have gone quiet.

The Challenge

Find customers who have ordered at least once but have not placed an order in the last 90 days (relative to 2024-03-28). Show their name, total number of orders, total amount spent, and the number of days since their last order.

Try your solution here

Discuss on Discord