GROUP BY + JOIN + ORDER BY to produce a segmented count
Planning the Q2 direct-mail drop. I need the count of active deposit customers by state. Deposit = Checking or Savings. Active = account status is `Open`. Show `state` and a count column called `n_customers`, sorted highest to lowest. Distinct customers — not accounts. One person in two states doesn't exist in our data model.
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| date_of_birth | TEXT | |
| state | TEXT | |
| onboarded_date | TEXT | |
| is_primary_only | INT |
| Column | Type | Key |
|---|---|---|
| account_id | INT | PK |
| customer_id | INT | FK → banking_customers |
| product_type | TEXT | |
| open_date | TEXT | |
| close_date | TEXT | |
| credit_limit | REAL | |
| current_fico | INT | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Customer state data and account product type are in separate tables. You want only customers with active deposit products — think about which account types qualify as "deposits" and how to connect them to the customer geography.
Filter: `a.product_type IN ('Checking','Savings')` and `a.status = 'Open'`.
`GROUP BY c.state`, `SELECT c.state, COUNT(DISTINCT c.customer_id) AS n_customers`, `ORDER BY n_customers DESC`. COUNT(DISTINCT ...) because a customer with both a Checking AND a Savings account would otherwise be counted twice.