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.
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.
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.
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.
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.
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.
| Approach | Complexity |
|---|---|
| Approach 1: Using Conditional Checks and Counters | Time Complexity: O(n), where n is the number of accounts. |
| Approach 2: Using Data Aggregation and Mapping Capabilities | Time Complexity: O(n), stemming from the sequential stream processing. |
| 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 |
Count Salary Categories | Leetcode 1907 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 7,575 views views
Watch 9 more video solutions →Practice Count Salary Categories with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor