# $1907. 按分类统计薪水
# https://leetcode.cn/problems/count-salary-categories/

# SQL架构
Create table If Not Exists Accounts (account_id int, income int);
Truncate table Accounts;
insert into Accounts (account_id, income) values ('3', '108939');
insert into Accounts (account_id, income) values ('2', '12747');
insert into Accounts (account_id, income) values ('8', '87709');
insert into Accounts (account_id, income) values ('6', '91796');

# Write your MySQL query statement below
select
    t.category,
    ifnull(a.cnt, 0) as accounts_count
from
    (
        select
            'Low Salary' as category
        union
        select
            'Average Salary'
        union
        select
            'High Salary'
    ) t
    left join (
        select
            case
                when income < 20000 then 'Low Salary'
                when income > 50000 then 'High Salary'
                else 'Average Salary'
            end as category,
            count(*) as cnt
        from
            Accounts
        group by
            category
    ) a on t.category = a.category;

# clean-up
drop table Accounts;