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'
);
Tested against PostgreSQL 16

Note

Self-join on orders to find consecutive month purchases. Great for customer loyalty analysis.

Tables referenced