Count Salary Categories - Problem
Given a table Accounts with bank account information, write a SQL solution to calculate the number of bank accounts for each salary category.
Salary Categories:
- "Low Salary": All salaries strictly less than $20,000
- "Average Salary": All salaries in the inclusive range [$20,000, $50,000]
- "High Salary": All salaries strictly greater than $50,000
Requirements:
- The result table must contain all three categories
- If there are no accounts in a category, return 0
- Return the result in any order
Table Schema
Accounts
| Column Name | Type | Description |
|---|---|---|
account_id
PK
|
int | Primary key - unique account identifier |
income
|
int | Monthly income for the bank account |
Primary Key: account_id
Note: Each row contains information about the monthly income for one bank account
Input & Output
Example 1 — Mixed Salary Categories
Input Table:
| account_id | income |
|---|---|
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
Output:
| category | accounts_count |
|---|---|
| Low Salary | 1 |
| Average Salary | 0 |
| High Salary | 3 |
💡 Note:
Account 2 has income $12,747 which is < $20,000 (Low Salary). Accounts 3, 8, and 6 have incomes > $50,000 (High Salary). No accounts fall in the Average Salary range, so it shows 0.
Example 2 — All Categories Represented
Input Table:
| account_id | income |
|---|---|
| 1 | 15000 |
| 2 | 25000 |
| 3 | 35000 |
| 4 | 75000 |
Output:
| category | accounts_count |
|---|---|
| Low Salary | 1 |
| Average Salary | 2 |
| High Salary | 1 |
💡 Note:
Account 1 ($15,000) is Low Salary. Accounts 2 and 3 ($25,000 and $35,000) are Average Salary. Account 4 ($75,000) is High Salary. All three categories have at least one account.
Example 3 — Boundary Values
Input Table:
| account_id | income |
|---|---|
| 1 | 20000 |
| 2 | 50000 |
| 3 | 19999 |
| 4 | 50001 |
Output:
| category | accounts_count |
|---|---|
| Low Salary | 1 |
| Average Salary | 2 |
| High Salary | 1 |
💡 Note:
Testing boundary conditions: $19,999 is Low Salary (< 20000), $20,000 and $50,000 are Average Salary (inclusive range), and $50,001 is High Salary (> 50000).
Constraints
-
1 ≤ account_id ≤ 10^6 -
0 ≤ income ≤ 10^6 - All account_id values are unique
Visualization
Tap to expand
Understanding the Visualization
1
Input
Accounts table with income data
2
Categorize
CASE WHEN classifies salaries
3
Count
Aggregate counts per category
Key Takeaway
🎯 Key Insight: Use conditional aggregation with CASE WHEN to ensure all categories appear in results, even with zero counts
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code