Hello Reader,

Imagine this: It's a Thursday afternoon and the CMO at your company just walked up to your desk: "I need a list of our high-value customers who haven't purchased recently. We're launching a win-back campaign Monday morning."

You have a few hours to get something usable.

Do you know how to find them?

Most analysts would panic, fumble through Excel, maybe run a few disconnected queries. But confident analysts? They write one query and deliver the answer in minutes.

Here's how.

The Business Problem

Summit Adventures (the fake adventure tourism company I created to help people learn business analytics) has a problem that every business faces: customers who spent thousands of dollars in the past but haven't come back in months.

These are proven buyers who already love your product. Business leaders know that getting existing customers to buy something again is 10x easier than finding new customers.

The trouble is finding them.

The One Query That Finds Them

Here's the SQL query I ran against our live Summit Adventures database this morning:

-- Find high-value customers who haven't booked recently
SELECT 
    c.first_name || ' ' || c.last_name AS customer_name,
    c.email,
    COUNT(DISTINCT b.booking_id) AS total_bookings,
    SUM(p.amount) AS total_spent,
    MAX(b.booking_date)::date AS last_booking_date,
    CURRENT_DATE::date - MAX(b.booking_date::date) AS days_since_last_booking
FROM customers c
    INNER JOIN bookings b ON c.customer_id = b.customer_id
    INNER JOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status = 'completed'
    AND b.status IN ('completed', 'confirmed')
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
HAVING SUM(p.amount) >= 3000  -- High-value threshold
    AND MAX(b.booking_date) < CURRENT_DATE - INTERVAL '180 days'
ORDER BY total_spent DESC
LIMIT 10;

Here's what we found for Summit Adventures:

• 10 customers who spent $3,000+ each
• They haven't booked in 6+ months (some nearly a year!)
• Combined lifetime value: over $140,000
• Top customer alone: over $19,000 in past bookings

That's thousands of dollars in revenue just sitting there, waiting for an email.

Breaking Down The Query

Lines 9-11: The JOINs We connect customers → bookings → payments to see who actually paid (not just browsed).

Lines 12-13: The WHERE Clause We filter for completed payments only. Draft bookings don't count.

Lines 15-16: The HAVING Clause Many analysts get tripped up on HAVING and WHERE. We filter AFTER grouping to find:

• Customers who spent $3,000+ total (high-value)
• Haven't booked in 180+ days (dormant)

WHERE filters individual rows. HAVING filters grouped results. You need HAVING here because you're filtering on SUM() and MAX(), both aggregate functions.

Why This Matters At Work

Let's say you run this at your company and find 50 dormant high-value customers who spent an average of $5,000 each.

If your win-back campaign gets just 10% of them to come back:

• 5 customers × $5,000 = $25,000 in recovered revenue
• Cost to send emails: $0
• Time to develop the insight: 30 minutes (if you know SQL)

That's the kind of analysis that gets you noticed by business leadership.

Common Mistakes to Avoid

Mistake #1: Using WHERE instead of HAVING

-- WRONG - This won't work
WHERE SUM(p.amount) >= 3000  -- Can't use aggregates in WHERE

Mistake #2: Forgetting to filter payment_status If you don't check for 'completed' payments, you'll count refunded or failed payments as revenue. Your numbers will be inflated and wrong.

Mistake #3: Not checking booking status Including cancelled bookings will give you a false picture of customer value.

Try This At Your Job

This pattern works for any business with repeat customers:

• SaaS companies → users who stopped paying
• Retail → customers who haven't purchased in 90 days
• Services → clients who haven't renewed contracts

Change the thresholds ($3000 and 180 days) to match your business.

The executives who get this list on Monday morning will ask one question: "How fast can we email them?"

You'll be the analyst who made it happen.

Until next time,

Brian (say hi on twitter!)

P.S. If you enjoyed this, you might like SQL for Business Impact, my course that teaches you how to write queries like this for real business problems. The launch promotion just ended, but you can join the waitlist for the next cohort at sqlforbusinessimpact.com.

P.P.S. Hit reply and let me know: what's one business question you wish you could answer with SQL? I read every response.