Skip to main content
Talentd

Count Salary Categories - Solution & Explanation

MediumDatabase11 min readAsked at: Amazon, Microsoft, Google +3
Practice this problem

Problem Statement

Table: Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

 

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:

  • "Low Salary": All the salaries strictly less than $20000.
  • "Average Salary": All the salaries in the inclusive range [$20000, $50000].
  • "High Salary": All the salaries strictly greater than $50000.

The result table must contain all three categories. If there are no accounts in a category, return 0.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Accounts 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              |
+----------------+----------------+
Explanation: 
Low Salary: Account 2.
Average Salary: No accounts.
High Salary: Accounts 3, 6, and 8.

Approach Overview

Problem Overview: You are given a database table of accounts with an income column. The task is to count how many accounts fall into three predefined salary bands: Low Salary (< 20000), Average Salary (20000–50000), and High Salary (> 50000). The output must return the category name and the number of accounts in each category, even if the count is zero.

Approach 1: Using Conditional Checks and Counters (O(n) time, O(1) space)

Scan the dataset once and evaluate each record with conditional checks on the income value. In SQL, this is typically implemented with CASE WHEN expressions combined with SUM or COUNT. Each condition acts like a counter: when the salary falls in a range, the corresponding counter increments. Since every row is processed exactly once, the time complexity is O(n), where n is the number of records. The space complexity remains O(1) because only three counters are maintained. This approach is straightforward and maps directly to how interviewers expect you to reason about conditional aggregation in a SQL query.

Approach 2: Using Data Aggregation and Mapping Capabilities (O(n) time, O(k) space)

Another strategy maps each income value to a category label first, then aggregates counts per category. In SQL, you create a derived column with a CASE statement that assigns 'Low Salary', 'Average Salary', or 'High Salary'. After mapping, run a GROUP BY aggregation to count records for each category. In application code (such as Java or Python), this mirrors building a dictionary or map where keys are category names and values are counters. The dataset is still scanned once, giving O(n) time complexity. Space complexity becomes O(k), where k is the number of categories (three in this case). This pattern is common in database analytics pipelines and data aggregation tasks.

Recommended for interviews: The conditional aggregation approach is the expected solution. It demonstrates that you understand how to perform categorized counting directly inside a SQL query using CASE WHEN expressions. The mapping plus aggregation approach is conceptually similar but slightly more abstract. Showing the direct counter-style aggregation first proves you understand how relational databases handle grouped metrics efficiently.

Approach 1: Approach 1: Using Conditional Checks and Counters

This approach involves iterating over the list of accounts, checking each account's income against the defined salary ranges, and incrementing counters for each category accordingly. The final step is to return a list or dictionary of these counts for each category.

The code defines a function countSalaryCategories that takes a 2D array accounts and its size as arguments. The function initializes three counters for low, average, and high salary categories. It iterates over the list of accounts, checking each account's income against the salary ranges, and increments the respective counter. Finally, it prints the counts in a formatted table.

Code

CC++JavaPythonC#JavaScript

C

C++

Java

Python

C#

JavaScript

Complexity

Time Complexity: O(n), where n is the number of accounts.
Space Complexity: O(1), as no additional space other than a few variables is used.

Try this approach in the editor →

Approach 2: Approach 2: Using Data Aggregation and Mapping Capabilities

This approach leverages data aggregation methods available in different languages, such as Java's streams, or Python's `collections` library to group and count salary categories, aiming for a more functional programming approach.

This Java implementation utilizes streams to categorize the salary of each account into either "Low Salary", "Average Salary", or "High Salary". The stream elements are grouped and counted using Collectors.groupingBy() and Collectors.counting(). Missing categories are handled by default values when fetched from the map.

Code

JavaPython

Java

Python

Complexity

Time Complexity: O(n), stemming from the sequential stream processing.
Space Complexity: O(m), where m is the number of unique salary categories, as it stores results in a map.

Try this approach in the editor →

Approach 3: Temporary Table + Grouping + Left Join

We can first create a temporary table containing all salary categories, and then count the number of bank accounts for each salary category. Finally, we use a left join to connect the temporary table with the result table to ensure that the result table contains all salary categories.

Code

MySQL

MySQL

Try this approach in the editor →

Approach 4: Filtering + Merging

We can filter out the number of bank accounts for each salary category separately, and then merge the results. Here, we use UNION to merge the results.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Approach 1: Using Conditional Checks and Counters

Time Complexity: O(n), where n is the number of accounts.
Space Complexity: O(1), as no additional space other than a few variables is used.

Approach 2: Using Data Aggregation and Mapping Capabilities

Time Complexity: O(n), stemming from the sequential stream processing.
Space Complexity: O(m), where m is the number of unique salary categories, as it stores results in a map.

Temporary Table + Grouping + Left Join—
Filtering + Merging—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Conditional Checks and CountersO(n)O(1)Best for SQL queries when counting fixed salary ranges directly in a single pass
Data Aggregation with Category MappingO(n)O(k)Useful when categories are generated dynamically or processed with GROUP BY logic

Video Solution

Count Salary Categories | Leetcode 1907 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 8,410 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Count Salary Categories easy or hard?
Count Salary Categories is typically considered a medium-level database problem. The challenge is recognizing that conditional aggregation with CASE WHEN can compute multiple categorized counts in a single query.
Count Salary Categories Python/Java solution
In Python or Java, you can iterate through income values, check which range each salary belongs to, and update counters stored in variables or a hash map. This mirrors the SQL conditional aggregation logic and runs in O(n) time.
How to solve Count Salary Categories in O(n)?
Use conditional aggregation. Apply CASE WHEN conditions to classify income values into Low, Average, or High salary groups, then aggregate counts with SUM or COUNT. Since each row is evaluated once during the scan, the query runs in O(n) time.
What is the best approach for Count Salary Categories?
The best approach uses conditional aggregation with CASE WHEN checks. Each salary range increments a counter using SUM or COUNT. The database scans the table once, resulting in O(n) time and O(1) extra space while producing all category counts in a single query.
Is Count Salary Categories asked at Google/Amazon/Meta?
Salary range aggregation problems appear frequently in database interview rounds at large tech companies. Variations of categorized counting and conditional aggregation are commonly used to evaluate SQL fundamentals in companies like Amazon and Meta.
What data structure is used in Count Salary Categories?
The SQL solution relies on relational table scanning and aggregation rather than traditional data structures. Conceptually, it behaves like three counters or a small map that tracks counts for Low, Average, and High salary categories.
What is the time complexity of Count Salary Categories?
The time complexity is O(n) because the database engine processes each row of the Accounts table once to evaluate the salary range conditions. Only a few counters are maintained, so the space complexity is O(1).

Ready to solve this problem?

Practice Count Salary Categories with our built-in code editor and test cases.

Practice on FleetCode
Previous

Create a Session Bar Chart