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
"Average Salary": All the salaries in the inclusive range
"High Salary": All the salaries strictly greater than
The result table must contain all three categories. If there are no accounts in a category, then report
Return the result table in any order.
The query result format is in the following example.
Problem Link – Salary Category
Difficulty Level – Medium
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