# SQL Interview Questions – Count Salary Categories

### 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.

## 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

Rating: 1 out of 5.

Posted in SQL