How does the churned customers query work?

Grouping by customer and taking MAX(order_date) gives each customer's last order. HAVING keeps only customers whose last order is more than 90 days old. Sorting by lifetime spend puts the most valuable lapsed customers first, which is where a win-back effort starts.

What commonly goes wrong with churned customers in SQL?

A fixed 90-day cutoff is wrong for products bought once a year; base the cutoff on each customer's usual gap.

Churned Customers in SQL

A churn list finds customers whose last activity is older than a cutoff, such as no order in 90 days, so you can see who has lapsed.

Example query

This example assumes: orders(id, customer_id bigint, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT customer_id,
       MAX(order_date) AS last_order_date,
       CURRENT_DATE - MAX(order_date) AS days_since_last_order,
       COUNT(*) AS lifetime_orders,
       SUM(total_amount) AS lifetime_spend
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) < CURRENT_DATE - 90
ORDER BY lifetime_spend DESC;

How it works

Grouping by customer and taking MAX(order_date) gives each customer's last order.
HAVING keeps only customers whose last order is more than 90 days old.
Sorting by lifetime spend puts the most valuable lapsed customers first, which is where a win-back effort starts.

MySQL, BigQuery, SQL Server and SQLite differences

Date arithmetic differs: MySQL uses CURDATE() - INTERVAL 90 DAY, SQL Server uses DATEADD(day, -90, CAST(GETDATE() AS date)), BigQuery uses DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY).

Common mistakes

A fixed 90-day cutoff is wrong for products bought once a year; base the cutoff on each customer's usual gap.
Customers who have never ordered do not appear; add an anti-join if they matter.
'Churned' for subscriptions is a cancellation date, not a gap in purchases; use the subscription table instead.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Which customers haven't ordered in the last 90 days, biggest spenders first?
How many customers lapsed last quarter?