Cohort CTE with within-window EXISTS
Quarterly onboarding funnel review. Pull every Checking account opened in Q3 2025 (open_date 2025-07-01 through 2025-09-30) and bucket by `acquisition_channel`. For each channel, show `cohort_size`, `converted` (count of accounts that had at least one ACH-In transaction with `counterparty_name LIKE '%PAYROLL%'` within 90 days of opening), and `conversion_pct` (converted/cohort_size * 100, rounded to 1 decimal). Sort by acquisition_channel ascending.
| 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 |
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| product_code | TEXT | |
| product_name | TEXT | |
| category | TEXT | |
| subcategory | TEXT | |
| default_apr | REAL | |
| default_apy | REAL | |
| monthly_fee | REAL | |
| nsf_fee | REAL | |
| od_fee | REAL |
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| household_id | TEXT | |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| state | TEXT | |
| segment | TEXT | |
| fico_current | INT | |
| estimated_income | REAL | |
| kyc_risk | TEXT | |
| acquisition_channel | TEXT | |
| onboarded_date | TEXT | |
| status | TEXT |
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
Build a cohort CTE: pick the Checking-account openings in the Q3 2025 window and carry `acquisition_channel` (from `retail_customers`) and `open_date` along. The 90-day window slides per-account, so you need each account's open_date on the row before the EXISTS check.
The conversion check is an EXISTS subquery on the transactions table — same account, payroll counterparty pattern, post_date inside the per-account 90-day window starting at open_date. Cast date columns as needed for the interval arithmetic.
GROUP BY acquisition_channel. Cohort size from COUNT(*), converted from a SUM of a 0/1 CASE on the EXISTS, rate as converted / cohort × 100 rounded to 1. Multiply by 100.0 (not 100) to force float math.