문제

source: LeetCode

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.

제출답안(MS SQL Server)

WITH
    ls_cat
        AS (SELECT category
            FROM (VALUES ('Low Salary'), ('Average Salary'), ('High Salary')) AS categories(category)),

    acc_cat
        AS (SELECT 
                account_id, 
                income, 
                CASE 
                    WHEN income BETWEEN 20000 AND 50000 THEN 'Average Salary'
                    WHEN income > 50000 THEN 'High Salary'
                    ELSE 'Low Salary' END AS category
            FROM
                accounts)

SELECT
    cat.category,
    ISNULL(grp.accounts_count, 0) AS accounts_count
FROM
    ls_cat AS cat
    LEFT JOIN (SELECT category, COUNT(account_id) AS accounts_count
                FROM acc_cat
                GROUP BY category) AS grp
        ON cat.category = grp.category

풀이(MS SQL Server)

CTE 1: 카테고리의 이름을 담은 칼럼 생성

WITH
    ls_cat
        AS (SELECT category
            FROM (VALUES ('Low Salary'), ('Average Salary'), ('High Salary')) AS categories(category)),

>> VALUES를 이용하여 가상의 테이블을 만들어냄

>> 가상의 테이블에 카테고리를 담을 칼럼의 Alias를 괄호 안에 입력해줌

CTE2: 계좌별 카테고리 입력

    acc_cat
        AS (SELECT 
                account_id, 
                income, 
                CASE 
                    WHEN income BETWEEN 20000 AND 50000 THEN 'Average Salary'
                    WHEN income > 50000 THEN 'High Salary'
                    ELSE 'Low Salary' END AS category
            FROM
                accounts)

메인쿼리

SELECT
    cat.category,
    ISNULL(grp.accounts_count, 0) AS accounts_count
FROM
    ls_cat AS cat
    LEFT JOIN (SELECT category, COUNT(account_id) AS accounts_count
                FROM acc_cat
                GROUP BY category) AS grp
        ON cat.category = grp.category

>> 카테고리가 모두 보여야하기 때문에 ls_cat (CTE1)을 기준으로 조인

>> accounts 테이블에 특정 카테고리 값이 없을 경우 NULL이 되므로 ISNULL을 이용하여 NULL 발생 시 0 으로 전환

>> acc_cat (CTE2)를 카테고리별로 그룹화한 뒤 ls_cat(CTE1)과 조

문제 바로가기(MS SQL Server)

728x90