Tables
erDiagram
Accounts {
account_id int PK
income int
}
Mermaid
복사
Question
Write a solution to calculate the number of bank accounts for 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, return 0.
Return the result table in any order.
The result format is in the following example.
DataFlow
case when 3 types salary(subquery) -> groupby salary categories
left join empty table
Answer
SELECT
c.category,
COALESCE(a.accounts_count, 0) AS accounts_count
FROM (
SELECT 'Low Salary' AS category
UNION ALL SELECT 'Average Salary'
UNION ALL SELECT 'High Salary'
) AS c
LEFT JOIN (
SELECT
CASE
WHEN income < 20000 THEN 'Low Salary'
WHEN income > 50000 THEN 'High Salary'
ELSE 'Average Salary'
END AS category,
COUNT(*) AS accounts_count
FROM Accounts
GROUP BY 1
) AS a
ON a.category = c.category
ORDER BY c.category;
SQL
복사