Multi-CTE EXISTS with cohort + per-month threshold
I'm sizing the primary-banking-relationship cohort for the 2026 Q1 review. Use this definition (anchor date 2025-12-01): (a) at least one ACH-In transaction with `counterparty_name LIKE '%PAYROLL%'` between 2025-10-02 and 2025-12-01 (the trailing 60-day window), AND (b) at least 5 debit transactions per month in each of September, October, and November 2025 (all three months must qualify). Return one row per qualifying customer — just `customer_id`. Sort ascending.
| Column | Type | Key |
|---|---|---|
| txn_id | INT | PK |
| account_id | INT | FK → retail_accounts |
| branch_id | INT | FK → retail_branches |
| post_date | TEXT | |
| channel | TEXT | |
| transaction_type | TEXT | |
| debit_credit | TEXT | |
| amount | REAL | |
| description | TEXT | |
| counterparty_name | TEXT | |
| is_cash_flag | INT | |
| is_reversal | INT |
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → retail_customers |
| product_id | INT | FK → retail_products |
| branch_id | INT | FK → retail_branches |
| open_date | TEXT | |
| close_date | TEXT | |
| status | TEXT | |
| ownership_type | TEXT | |
| interest_rate | REAL | |
| original_balance | REAL | |
| credit_limit | REAL | |
| fico_at_orig | INT | |
| maturity_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two CTEs make this readable. CTE 1: pick out account_ids with at least one PAYROLL ACH-In in the trailing-60-day window. CTE 2: count debits per (account_id, month) and keep the months where the count is ≥ 5.
After the CTEs, an account qualifies if it appears in CTE 1 AND its month-set in CTE 2 covers all three months (Sep/Oct/Nov 2025). `HAVING COUNT(DISTINCT month) = 3` after grouping CTE 2 by account_id is the cleanest gate.
Final SELECT joins back to `retail_accounts` to swap account_id for customer_id, with DISTINCT (a customer with two qualifying accounts should only appear once). The output is a single column.