-- 32_anti_join_inactive_customers.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Anti‑join to find customers with no activity.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.
SET search_path = bank, public;

-- Anti‑join: customers with no transactions in last 90 days
SELECT c.* FROM bank.customers c
WHERE NOT EXISTS (
  SELECT 1 FROM bank.accounts a JOIN bank.transactions t ON t.account_id = a.account_id
  WHERE a.customer_id = c.customer_id AND t.created_at > now() - INTERVAL '90 days'
);
