Count Salary Categories

Count Salary Categories

What is this problem about?

The "Count Salary Categories interview question" is a SQL data reporting challenge. You are given an Accounts table with employee IDs and their monthly salaries. You need to categorize each account into one of three buckets: "Low Salary" (less than 20,000), "Average Salary" (20,000 to 50,000 inclusive), and "High Salary" (greater than 50,000). The final report must show all three categories and the count of accounts in each, even if a category has zero accounts.

Why is this asked in interviews?

Companies like Amazon and Bloomberg use the "Count Salary Categories coding problem" to test a candidate's ability to perform Conditional Aggregation and handle "missing" data categories. A simple GROUP BY will only return categories that exist in the data. This problem evaluates whether you know how to use UNION or a reference table to ensure all required labels appear in the result.

Algorithmic pattern used

This problem follows the Union of Aggregates pattern.

1. Individual Queries: Write three separate SELECT statements, one for each category.
   • SELECT 'Low Salary' as category, COUNT(*) as accounts_count FROM Accounts WHERE income < 20000
   • Repeat for "Average Salary" ($20000 \leq ext{income} \leq 50000$) and "High Salary" ($ext{income} > 50000$).
2. Union All: Combine these three results using UNION ALL.
3. Alternative: Use a common table expression (CTE) or a manual table of categories joined with the aggregated counts.

Example explanation

Table: (1, 10000), (2, 60000), (3, 70000)

• Low Salary: 1 account.
• Average Salary: 0 accounts.
• High Salary: 2 accounts. Result:
• Low Salary: 1
• Average Salary: 0
• High Salary: 2

Common mistakes candidates make

• Using GROUP BY only: Using a CASE WHEN inside a GROUP BY. This will skip categories that have zero entries (like "Average Salary" in the example above).
• Incorrect Boundaries: Messing up the "inclusive" vs "exclusive" rules for the salary ranges.
• Join Logic: Attempting a complex outer join without a base set of categories.

Interview preparation tip

When a SQL problem requires returning labels that might not exist in the source data, think UNION ALL. It is the most robust way to "force" specific rows into your output. This is a common "Database interview pattern" for financial reporting.