All examples
Query · SQL

Dormant customers — no purchase in X days

Active customers who've gone quiet — with days since their last order plus lifetime orders and spend, for a prioritized win-back list.

Type: Query Output: TableData: Demo sandbox

What people ask

The query

dormant-customers.sql
SELECT c.name AS customer_name, c.number AS customer_number,
  MAX(so.dateissued) AS last_order_date,
  DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) AS days_dormant,
  COUNT(so.id) AS total_orders,
  ROUND(SUM(so.totalprice),2) AS total_spent
FROM customer c
LEFT JOIN so ON so.customerid = c.id AND so.statusid IN (20,25,60,70)
WHERE c.activeflag = 1
GROUP BY c.id, c.name, c.number
HAVING MAX(so.dateissued) IS NOT NULL
   AND DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) >= 90
ORDER BY days_dormant DESC;

Preview · demo data

3 rows · demo data
CustomerLast orderDays dormantOrdersTotal spent
Acme Bikes2025-12-0220047$128,400.00
Globex Cycles2026-01-1515612$34,210.00
Initech Sports2026-02-201208$19,880.00

Notes & gotchas

A locked session — the real conversation that produced this report. Open it in the app to fork and continue.
Which customers are dormant — haven't purchased in the last 90 days?
Agent Query session

I'll take active customers, find each one's most recent issued order, and keep those past your threshold. I'll also pull lifetime orders and spend so you can prioritize the valuable lapsed accounts. I've made the 90 days a parameter so you can adjust it.

dormant-customers.sql
SELECT c.name AS customer_name, c.number AS customer_number,
  MAX(so.dateissued) AS last_order_date,
  DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) AS days_dormant,
  COUNT(so.id) AS total_orders,
  ROUND(SUM(so.totalprice),2) AS total_spent
FROM customer c
LEFT JOIN so ON so.customerid = c.id AND so.statusid IN (20,25,60,70)
WHERE c.activeflag = 1
GROUP BY c.id, c.name, c.number
HAVING MAX(so.dateissued) IS NOT NULL
   AND DATEDIFF(CURRENT_DATE, MAX(so.dateissued)) >= 90
ORDER BY days_dormant DESC;
Agent Query session

Here's the win-back list on demo data, most-dormant first. Bump Days_Dormant to 180 for a stricter cut, or I can include never-ordered customers too.

preview · demo data
CustomerLast orderDays dormantOrdersTotal spent
Acme Bikes2025-12-0220047$128,400.00
Globex Cycles2026-01-1515612$34,210.00
Initech Sports2026-02-201208$19,880.00

Want one like this for your Fishbowl?

Send a short brief — an ILC expert builds and tests it, and you get the file ready to import. The quote is free.

Related examples