JOIN + WHERE + GROUP BY to aggregate across two tables
Compliance wants a state-by-state headcount of customers with at least one open account — they're sizing regional SAR review capacity. Join `aml_customers` to `aml_accounts`, keep only rows where the account's `status` is `'open'`, count distinct customers per state, and order from most to least. Don't double-count a customer who has two open accounts.
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| TEXT | ||
| phone | TEXT | |
| state | TEXT | |
| status | TEXT | |
| onboarded_date | TEXT | |
| last_kyc_refresh | TEXT |
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → aml_customers |
| product_type | TEXT | |
| open_date | TEXT | |
| close_date | TEXT | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Start with an INNER JOIN on `customer_id` so you only keep customers who actually have an account row.
Filter accounts on `status = 'open'` in the WHERE clause. Don't put it in an ON clause — the semantics are the same here but WHERE is the convention for LEFT-JOIN-safe code later.
Use `COUNT(DISTINCT c.customer_id)` so a customer with both a checking AND savings account counts once. Group by `c.state`, order by the count descending.