[SQL 문제 풀이] Count Salary Categories (급여 카테고리 수)

Count Salary Categories - LeetCode

Can you solve this real interview question? Count Salary Categories - Table: Accounts +-------------+------+ | Column Name | Type | +-------------+------+ | account_id | int | | income | int | +-------------+------+ account_id is the primary key (column with unique values) for this table. Each row contains information about the monthly income for one bank account. 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. Example 1: Input: Accounts table: +------------+--------+ | account_id | income | +------------+--------+ | 3 | 108939 | | 2 | 12747 | | 8 | 87709 | | 6 | 91796 | +------------+--------+ Output: +----------------+----------------+ | category | accounts_count | +----------------+----------------+ | Low Salary | 1 | | Average Salary | 0 | | High Salary | 3 | +----------------+----------------+ Explanation: Low Salary: Account 2. Average Salary: No accounts. High Salary: Accounts 3, 6, and 8.

https://leetcode.com/problems/count-salary-categories/description/


각 급여 카테고리에 대한 은행 계좌 수를 계산하는 솔루션을 작성합니다.
급여 카테고리는 다음과 같습니다:

* "Low Salary": 모든 급여는 엄밀히 말하면 2만 달러 미만입니다.
* "Average Salary": 포함된 모든 급여 [$20,000, $500,000].
* "High Salary": 모든 급여가 5만 달러를 초과합니다.
* 결과 테이블에는 세 가지 카테고리가 모두 포함되어야 합니다.
	카테고리에 계정이 없는 경우 0을 반환합니다.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

MySQL


SELECT 
    c.category,
    COUNT(a.account_id) AS accounts_count
FROM (
		-- 더미 테이블 생성
    SELECT 'Low Salary' AS category
    UNION ALL
    SELECT 'Average Salary'
    UNION ALL
    SELECT 'High Salary'
    ) AS c
LEFT JOIN Accounts a -- 각 범주에 해당하는 월급 JOIN
    ON (c.category = 'Low Salary' AND a.income < 20000)
    OR (c.category = 'Average Salary' AND a.income BETWEEN 20000 AND 50000)
    OR (c.category = 'High Salary' AND a.income > 50000)
GROUP BY 
    c.category;

Oracle


SELECT 
    c.category,
    COUNT(a.account_id) AS accounts_count
FROM (
    SELECT 'Low Salary' AS category FROM dual
    UNION ALL
    SELECT 'Average Salary' FROM dual
    UNION ALL
    SELECT 'High Salary' FROM dual
    ) c
LEFT JOIN Accounts a
    ON (c.category = 'Low Salary' AND a.income < 20000)
    OR (c.category = 'Average Salary' AND a.income BETWEEN 20000 AND 50000)
    OR (c.category = 'High Salary' AND a.income > 50000)
GROUP BY 
    c.category;