1907. Count Salary Categories Solution

The problem

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.

Python

def count_salary_categories(accounts):
    low_salary_count = 0
    avg_salary_count = 0
    high_salary_count = 0

    for account in accounts:
        income = account[1]
        if income < 20000:
            low_salary_count += 1
        elif income <= 50000:
            avg_salary_count += 1
        else:
            high_salary_count += 1

    table_format = (
        "+----------------+----------------+\n"
        "| category       | accounts_count |\n"
        "+----------------+----------------+\n"
        "| Low Salary     | {}              |\n"
        "| Average Salary | {}              |\n"
        "| High Salary    | {}              |\n"
        "+----------------+----------------+"
    )
    print(table_format.format(low_salary_count, avg_salary_count, high_salary_count))

accounts = [(3, 108939), (2, 12747), (8, 87709), (6, 91796)]
count_salary_categories(accounts)

Java

import java.util.Arrays;

public class Main {

    public static void countSalaryCategories(int[][] accounts) {
        int lowSalaryCount = 0, avgSalaryCount = 0, highSalaryCount = 0;

        for (int[] account : accounts) {
            int income = account[1];
            if (income < 20000) {
                lowSalaryCount++;
            } else if (income <= 50000) {
                avgSalaryCount++;
            } else {
                highSalaryCount++;
            }
        }

        System.out.println("+----------------+----------------+");
        System.out.println("| category       | accounts_count |");
        System.out.println("+----------------+----------------+");
        System.out.println("| Low Salary     | " + lowSalaryCount + "              |");
        System.out.println("| Average Salary | " + avgSalaryCount + "              |");
        System.out.println("| High Salary    | " + highSalaryCount + "              |");
        System.out.println("+----------------+----------------+");
    }

    public static void main(String[] args) {
        int[][] accounts = {{3, 108939}, {2, 12747}, {8, 87709}, {6, 91796}};
        countSalaryCategories(accounts);
    }
}

C++

#include <iostream>
#include <vector>
using namespace std;

void countSalaryCategories(const vector<pair<int, int>>& accounts) {
    int lowSalaryCount = 0, avgSalaryCount = 0, highSalaryCount = 0;
    
    for (const auto& account : accounts) {
        int income = account.second;
        if (income < 20000) {
            lowSalaryCount++;
        } else if (income <= 50000) {
            avgSalaryCount++;
        } else {
            highSalaryCount++;
        }
    }

    cout << "+----------------+----------------+\n";
    cout << "| category       | accounts_count |\n";
    cout << "+----------------+----------------+\n";
    cout << "| Low Salary     | " << lowSalaryCount << "              |\n";
    cout << "| Average Salary | " << avgSalaryCount << "              |\n";
    cout << "| High Salary    | " << highSalaryCount << "              |\n";
    cout << "+----------------+----------------+\n";
}

int main() {
    vector<pair<int, int>> accounts = {{3, 108939}, {2, 12747}, {8, 87709}, {6, 91796}};
    countSalaryCategories(accounts);
    return 0;
}

JavaScript

function countSalaryCategories(accounts) {
    let lowSalaryCount = 0;
    let avgSalaryCount = 0;
    let highSalaryCount = 0;

    accounts.forEach((account) => {
        const income = account[1];
        if (income < 20000) {
            lowSalaryCount++;
        } else if (income <= 50000) {
            avgSalaryCount++;
        } else {
            highSalaryCount++;
        }
    });

    console.log('+----------------+----------------+');
    console.log('| category       | accounts_count |');
    console.log('+----------------+----------------+');
    console.log(`| Low Salary     | ${lowSalaryCount}              |`);
    console.log(`| Average Salary | ${avgSalaryCount}              |`);
    console.log(`| High Salary    | ${highSalaryCount}              |`);
    console.log('+----------------+----------------+');
}

const accounts = [[3, 108939], [2, 12747], [8, 87709], [6, 91796]];
countSalaryCategories(accounts);

#include <stdio.h>

void countSalaryCategories(int accounts[][2], int accountsSize) {
    int lowSalaryCount = 0, avgSalaryCount = 0, highSalaryCount = 0;

    for (int i = 0; i < accountsSize; i++) {
        int income = accounts[i][1];
        if (income < 20000) {
            lowSalaryCount++;
        } else if (income <= 50000) {
            avgSalaryCount++;
        } else {
            highSalaryCount++;
        }
    }

    printf("+----------------+----------------+\n");
    printf("| category       | accounts_count |\n");
    printf("+----------------+----------------+\n");
    printf("| Low Salary     | %d              |\n", lowSalaryCount);
    printf("| Average Salary | %d              |\n", avgSalaryCount);
    printf("| High Salary    | %d              |\n", highSalaryCount);
    printf("+----------------+----------------+\n");
}

int main() {
    int accounts[][2] = {{3, 108939}, {2, 12747}, {8, 87709}, {6, 91796}};
    int accountsSize = sizeof(accounts)/sizeof(accounts[0]);
    countSalaryCategories(accounts, accountsSize);
    return 0;
}

using System;

public class Program {
    public static void CountSalaryCategories(int[,] accounts) {
        int lowSalaryCount = 0, avgSalaryCount = 0, highSalaryCount = 0;

        for (int i = 0; i < accounts.GetLength(0); i++) {
            int income = accounts[i, 1];
            if (income < 20000) {
                lowSalaryCount++;
            } else if (income <= 50000) {
                avgSalaryCount++;
            } else {
                highSalaryCount++;
            }
        }

        Console.WriteLine("+----------------+----------------+");
        Console.WriteLine("| category       | accounts_count |");
        Console.WriteLine("+----------------+----------------+");
        Console.WriteLine($"| Low Salary     | {lowSalaryCount}              |");
        Console.WriteLine($"| Average Salary | {avgSalaryCount}              |");
        Console.WriteLine($"| High Salary    | {highSalaryCount}              |");
        Console.WriteLine("+----------------+----------------+");
    }

    public static void Main() {
        int[,] accounts = {{3, 108939}, {2, 12747}, {8, 87709}, {6, 91796}};
        CountSalaryCategories(accounts);
    }
}

This Python function, count_salary_categories, loops through a given list of tuples representing bank accounts and their incomes. It uses conditional checks to categorize each income, updating counters accordingly. It then prints out the results in a structured format.

Complexity

Time Complexity: O(n), iterates through n accounts.
Space Complexity: O(1); uses constant space for tracking.

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.

Python

from collections import Counter

def count_salary_categories(accounts):
    categories = [
        "Low Salary" if income < 20000 else
        "Average Salary" if income <= 50000 else
        "High Salary"
        for _, income in accounts
    ]
    
    category_counts = dict(Counter(categories))

    table_format = (
        "+----------------+----------------+\n"
        "| category       | accounts_count |\n"
        "+----------------+----------------+\n"
        "| Low Salary     | {}              |\n"
        "| Average Salary | {}              |\n"
        "| High Salary    | {}              |\n"
        "+----------------+----------------+"
    )
    print(table_format.format(
        category_counts.get("Low Salary", 0),
        category_counts.get("Average Salary", 0),
        category_counts.get("High Salary", 0)
    ))

accounts = [(3, 108939), (2, 12747), (8, 87709), (6, 91796)]
count_salary_categories(accounts)

Java

import java.util.Arrays;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

public class Main {

    public static void main(String[] args) {
        int[][] accounts = {{3, 108939}, {2, 12747}, {8, 87709}, {6, 91796}};

        Map<String, Long> categoryCounts = Arrays.stream(accounts)
            .map(account -> {
                int income = account[1];
                if (income < 20000) return "Low Salary";
                else if (income <= 50000) return "Average Salary";
                else return "High Salary";
            })
            .collect(Collectors.groupingBy(Function.identity(), Collectors.counting()));

        System.out.println("+----------------+----------------+");
        System.out.println("| category       | accounts_count |");
        System.out.println("+----------------+----------------+");
        System.out.printf("| Low Salary     | %d              |\n", categoryCounts.getOrDefault("Low Salary", 0L));
        System.out.printf("| Average Salary | %d              |\n", categoryCounts.getOrDefault("Average Salary", 0L));
        System.out.printf("| High Salary    | %d              |\n", categoryCounts.getOrDefault("High Salary", 0L));
        System.out.println("+----------------+----------------+");
    }
}

In this Python approach, a list comprehension categorizes each income, and collections.Counter counts occurrences for each category in just one pass. Default values of zero are used for categories not present in the result.

Complexity

Time Complexity: O(n), as every account is processed once.
Space Complexity: O(m) due to storage of results in a dictionary, where m is the number of distinct categories.

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.

MySQL

# Write your MySQL query statement below
WITH
    S AS (
        SELECT 'Low Salary' AS category
        UNION
        SELECT 'Average Salary'
        UNION
        SELECT 'High Salary'
    ),
    T AS (
        SELECT
            CASE
                WHEN income < 20000 THEN "Low Salary"
                WHEN income > 50000 THEN 'High Salary'
                ELSE 'Average Salary'
            END AS category,
            COUNT(1) AS accounts_count
        FROM Accounts
        GROUP BY 1
    )
SELECT category, IFNULL(accounts_count, 0) AS accounts_count
FROM
    S
    LEFT JOIN T USING (category);

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.

MySQL

# Write your MySQL query statement below
SELECT 'Low Salary' AS category, IFNULL(SUM(income < 20000), 0) AS accounts_count FROM Accounts
UNION
SELECT
    'Average Salary' AS category,
    IFNULL(SUM(income BETWEEN 20000 AND 50000), 0) AS accounts_count
FROM Accounts
UNION
SELECT 'High Salary' AS category, IFNULL(SUM(income > 50000), 0) AS accounts_count FROM Accounts;

Try this approach in the editor

Complexity comparison

Approach	Complexity
Approach 1: Using Conditional Checks and Counters	Time Complexity: O(n), iterates through n accounts. Space Complexity: O(1); uses constant space for tracking.
Approach 2: Using Data Aggregation and Mapping Capabilities	Time Complexity: O(n), as every account is processed once. Space Complexity: O(m) due to storage of results in a dictionary, where m is the number of distinct categories.
Temporary Table + Grouping + Left Join	–
Filtering + Merging	–

Complexity analysis

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

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).

Count Salary Categories solution and explanation

The problem

Approach overview

Approach 1: Approach 1: Using Conditional Checks and Counters

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

Approach 3: Temporary Table + Grouping + Left Join

Approach 4: Filtering + Merging

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Count Salary Categories solution and explanation

The problem

Approach overview

Approach 1: Approach 1: Using Conditional Checks and Counters

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

Approach 3: Temporary Table + Grouping + Left Join

Approach 4: Filtering + Merging

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems