1907. Count Salary Categories

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.

 

 

# 처음 풀이

with base_table as(
  select account_id
       , case when income < 20000 then 'Low Salary'
              when income between 20000 and 50000 then 'Average Salary'
              when income > 50000 then 'High Salary'
              end as category
    from Accounts
)

select category
     , count(category) as accounts_count
  from base_table
  group by category;
=====================================================

# output
+-------------+----------------+
| category    | accounts_count |
+-------------+----------------+
| High Salary | 3              |
| Low Salary  | 1              |
+-------------+----------------+

 

처음엔 위의 쿼리를 작성했지만, 문제점이 바로 드러났다.

카테고리 별로 계좌 수가 0인 경우는 포함되지 않는다는 것.

 

 

아래와 같이 풀어야 제대로 다 포함될 수 있다.

# 고친 풀이

select 'Low Salary' as category
     , sum(if(income < 20000, 1, 0)) as accounts_count
  from Accounts
union all
select 'Average Salary' as category
     , sum(if(income between 20000 and 50000, 1, 0)) as accounts_count
  from Accounts
union all
select 'High Salary' as category
     , sum(if(income > 50000, 1, 0)) as accounts_count
  from Accounts;
========================================================
# output

+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+