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.
The key idea in #1907 Count Salary Categories is to classify each record into predefined salary ranges and then count how many accounts fall into each category. Typically, the dataset contains an income column, and the task is to group records into three buckets: low, average, and high salary ranges.
A common SQL approach is to use conditional aggregation. By applying SUM(CASE WHEN ... THEN 1 ELSE 0 END), we can count how many rows fall into each salary bracket in a single scan of the table. Another straightforward method is to run separate COUNT queries for each category and combine them using UNION ALL, ensuring that each category appears in the final result.
The goal is to return all categories even if their count is zero. Since the table is scanned only once in the optimized approach, the time complexity is O(n), where n is the number of rows in the table, while the space usage remains O(1).
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| Conditional Aggregation with CASE | O(n) | O(1) |
| Multiple COUNT Queries with UNION ALL | O(n) | O(1) |
Learn With Chirag
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.
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.
1function countSalaryCategories(accounts) {
2 let lowSalaryCount = 0;
3 let avgSalaryCount = 0;
4 let highSalaryCount = 0;
5
The JavaScript function countSalaryCategories iterates over a list of accounts. For each account, it examines the income and updates relevant counters based on which salary range the income falls into. It then logs the results in a formatted manner.
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.
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.
1import java.util.Arrays;
2
Watch expert explanations and walkthroughs
Jot down your thoughts, approach, and key learnings
Yes, problems like this are common in data-focused or SQL interview rounds. They test your ability to categorize data, use conditional logic, and perform efficient aggregations in SQL.
The optimal approach uses conditional aggregation with CASE statements in SQL. This allows counting all salary categories in a single table scan, making the query efficient and easy to maintain.
You can explicitly define each category using UNION ALL queries or structure the query so each category is selected independently. This guarantees that every category appears even if no rows fall into it.
Conditional aggregation using CASE expressions is the most useful SQL concept for this problem. It helps classify rows into categories and count them efficiently without multiple table scans.
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.