Count Salary Categories | Leetcode 1907 | Crack SQL Interviews in 50 Qs

1. Problem Overview & Objective

In this session, we tackle LeetCode Problem 1907: Count Salary Categories, part of the 50-question SQL interview preparation series. The core objective is to calculate the number of bank accounts falling into three distinct salary categories—Low Salary, Average Salary, and High Salary—based on monthly income values stored in an Accounts table.

A critical nuance of this problem is that even if a category contains zero accounts, it must still appear in the final result table with a count of 0. Missing categories cannot be omitted.

2. Database Schema & Input Data

We are provided with a single table named Accounts. Below is the structural definition and data layout:

• Table Name: Accounts
• Primary Key: account_id (Unique integer values)
• Data Columns:
  • account_id (Integer): Unique identifier for each bank account.
  • income (Integer): The monthly income associated with the bank account.

Example Accounts Table:

3. Business Rules & Salary Category Definitions

The problem statement defines precise mathematical boundaries for each category:

1. Low Salary: All salaries strictly less than $20000. ($< 20000$)
2. Average Salary: All salaries inclusive between $20000 and $50000. ($20000 \le \text{income} \le 50000$)
3. High Salary: All salaries strictly greater than $50000. ($> 50000$)

4. Manual Walkthrough & Example Evaluation

Let's evaluate our sample Accounts table against the business rules row by row:

• Low Salary Evaluation: Only account_id = 2 has an income of $10,000 ($< 20000$).
  • Count = 1
• Average Salary Evaluation: Only account_id = 3 has an income of $45,000 ($20000 \le 45000 \le 50000$).
  • Count = 1 (Correction note based on transcript walkthrough: transcript checks 4 accounts where ID 1 is 150k, ID 2 is 10k, ID 3 is 45k, ID 4 is 70k. Let's verify counts: Low = 1 (ID 2), Average = 1 (ID 3), High = 2 (ID 1, ID 4).
• High Salary Evaluation: account_id = 1 ($150,000) and account_id = 4 ($70,000) are both $> 50000$.
  • Count = 2

Expected Output Table Structure:

5. Naive Approach vs. The UNION Strategy

When dealing with categorical aggregation where certain categories might return 0 rows, a standard GROUP BY query on the table alone can fail. If no rows match Average Salary, a simple GROUP BY category query will omit the Average Salary row entirely rather than returning 0.

To guarantee that all three categories (Low Salary, Average Salary, High Salary) always appear in the output, we can construct three separate aggregate queries (one for each category) and combine them using the UNION operator.

6. Step-by-Step Query Construction

Step 6.1: Writing the Low Salary Query

We select a literal string 'Low Salary' and alias it as category, then use COUNT(income) filtered by the WHERE clause:

SELECT 'Low Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income < 20000;

Step 6.2: Writing the Average Salary Query

We apply the inclusive boundary condition using the logical AND operator:

SELECT 'Average Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income >= 20000 AND income <= 50000;

Step 6.3: Writing the High Salary Query

We check for strict inequality greater than $50000:

SELECT 'High Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income > 50000;

7. Complete Combined Solution (UNION Approach)

By chaining these three independent queries together with UNION, we ensure that even if a category yields zero matching rows, the SQL engine returns a row with a count of 0.

SELECT 'Low Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income < 20000

UNION

SELECT 'Average Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income >= 20000 AND income <= 50000

UNION

SELECT 'High Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income > 200000; -- (or > 50000 as per rule)

Corrected Final Production Query:

SELECT 'Low Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income < 20000

UNION

SELECT 'Average Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income >= 20000 AND income <= 50000

UNION

SELECT 'High Salary' AS category, COUNT(income) AS accounts_count
FROM Accounts
WHERE income > 50000;

8. Execution Flow Walkthrough

9. Alternative Approaches & Performance Considerations

While the UNION approach is intuitive, highly readable, and ideal for interviews, it requires scanning the Accounts table three separate times.

• UNION Approach:
  • Time Complexity: $3 \times O(N)$ where $N$ is the number of rows in Accounts.
  • Pros: Extremely easy to write, perfectly handles zero-count edge cases.
  • Cons: Multiple table scans.
• Conditional Aggregation Approach (Alternative): Using SUM(CASE WHEN ... THEN 1 ELSE 0 END) with a single table scan:
  SQL

  Copy

  SELECT 'Low Salary' AS category, SUM(income < 20000) AS accounts_count FROM Accounts
  UNION
  SELECT 'Average Salary', SUM(income >= 20000 AND income <= 50000) FROM Accounts
  UNION
  SELECT 'High Salary', SUM(income > 50000) FROM Accounts;
  

10. Summary and Key Takeaways

• Mandatory Zero-Row Handling: When interview problems require displaying categories with 0 counts, standard GROUP BY statements can drop missing categories, requiring explicit UNION or cross-join patterns.
• Literal Aliasing: Use constant strings like 'Low Salary' AS category to inject custom category names directly into the result set.
• Boundary Precision: Pay close attention to inclusive (>=, <=) versus strict (<, >) inequality signs specified in the problem requirements.
• The UNION Operator: Combines the result sets of two or more SELECT statements into a single cohesive output table.
• Aliasing Aggregate Functions: Always use AS to give clear, descriptive alias names (e.g., accounts_count) to calculated columns.
• Table Scans: Be mindful that using multiple UNION blocks performs multiple scans over the source table, which is acceptable for moderate datasets but worth optimizing with conditional sums for massive tables.