COUNT vs COUNT DISTINCT to reason about table grain
The CFO asked why our 'customer count' in the board deck is smaller than our 'account count', and my team gave three different answers. Please end this. One query against `banking_accounts`: total account rows vs distinct customer_ids. Call them `total_accounts` and `unique_customers`. I want this as evidence for the next Teams thread.
| 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.
You need two different counts from the same table — total rows, and unique customers. Think about what the difference between those two numbers tells you about how many accounts each customer holds on average.
Two aggregates in a single SELECT — one COUNT(*) for total rows and one COUNT(DISTINCT customer_id) for unique customers — both aliased to the names the briefing called out.
Single row, two columns. The ratio is the average number of accounts per customer — useful context to drop into your reply.