[LeetCode] 1907. Count Salary Categories

출력해야 하는 항목을 고정시킬 때, union all과 select 을 써서 left/right join 을 해야합니다.

 

Question

 

 

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.

 

 

Code

 

with count_acc as(
    select 
        case
            when income < 20000 then 'Low Salary'
            when (20000 <= income) and (income <= 50000) then 'Average Salary'
            when 50000 < income then 'High Salary'
            -- else 'Average Salary'
            end as "category"
    from accounts
)
select j.category
        , count(c.category) as accounts_count
from count_acc c
right join (select 'Low Salary' as category
            union all 
            select 'Average Salary' as category
            union all 
            select 'High Salary' as category
            ) j on c.category = j.category
group by 1
;

 

My code