LEFT JOIN + GROUP BY to aggregate child rows onto a filtered parent
Compliance wants a list of our top joint-capable customers by account footprint. Pull customers where `is_primary_only = 0` — these are folks who appear as a joint or authorized user on at least one account somewhere — and show their customer_id, first_name, last_name, and the count of accounts they currently own. Sort by count desc and by customer_id asc for tie-breaking. Top 10 only.
| 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.
LEFT JOIN, not JOIN — some joint-capable customers have zero booked accounts (they're on someone else's account, not their own), and an inner JOIN would silently drop them.
GROUP BY all three customer fields in the SELECT. `COUNT(a.account_id)` — counting the JOINed column — returns 0 for customers with no accounts. `COUNT(*)` would return 1 due to the LEFT JOIN padding row.
GROUP BY all three customer fields in the SELECT, COUNT the joined account_id (so zero-account customers come back as 0, not 1), filter is_primary_only = 0, then ORDER BY the count DESC with customer_id as a tiebreaker, LIMIT 10.