1907. Count Salary Categories
Description
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.
Solutions
Solution 1: 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
|
Solution 2: 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.
1 2 3 4 5 6 7 8 9 |
|