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.

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