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
Count Salary Categories INPUT Accounts Table account_id income 1 $15,000 2 $35,000 3 $75,000 4 $45,000 5 $10,000 Salary Categories: Low: < $20,000 Avg: $20K - $50K High: > $50,000 ALGORITHM STEPS 1 Create Categories Generate all 3 category names using UNION 2 CASE Statement Classify each account by income range 3 LEFT JOIN Join categories with classified accounts 4 COUNT + COALESCE Count per category, use 0 for empty SELECT category, COUNT(income) FROM Categories c LEFT JOIN Accounts GROUP BY category FINAL RESULT Output Table category count Low Salary 2 Average Salary 2 High Salary 1 Distribution Summary Low 2 Avg 2 High 1 OK - Complete Key Insight: Use UNION to create a virtual table with all category names first. This ensures all three categories appear in results even when count is 0. LEFT JOIN preserves categories without matching accounts, and COUNT(column) ignores NULLs, naturally returning 0 for empty categories. TutorialsPoint - Count Salary Categories | Optimal Solution

Related Problems

Asked in
Amazon 15 Facebook 12 Microsoft 8
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen