Problem Description –
Write an SQL query to report the number of bank accounts of 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, then report 0
.
Return the result table in any order.
The query result format is in the following example.

Problem Link – Salary Category
Difficulty Level – Medium
Solution –
with t1 as (
SELECT
category,
COUNT(*) as accounts_count
FROM (
SELECT
account_id,
income,
CASE WHEN income > 50000 THEN 'High Salary'
WHEN income >= 20000 AND income <= 50000 THEN 'Average Salary'
WHEN income < 20000 THEN 'Low Salary'
END as category
FROM Accounts
) x
GROUP BY 1
), t2 as (
SELECT 'Low Salary' as category
UNION
SELECT 'Average Salary'
UNION
SELECT 'High Salary'
)
SELECT
t2.category,
IFNULL(accounts_count, 0) as accounts_count
FROM t2 LEFT JOIN t1
ON t2.category = t1.category