AdvancedSubqueries & EXISTS
Find customers with orders in consecutive months
The query
SQL
SELECT DISTINCT c.name, c.email
FROM customers c
WHERE c.cust_id IN (
SELECT o1.cust_id
FROM orders o1
JOIN orders o2
ON o1.cust_id = o2.cust_id
AND DATE_TRUNC('month', o2.order_date) =
DATE_TRUNC('month', o1.order_date) + INTERVAL '1 month'
);Note
“Self-join on orders to find consecutive month purchases. Great for customer loyalty analysis.