[SQL_Q] 1907. Count Salary Categories

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

문제

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.

내 쿼리

WITH grp_cnt as (
    SELECT grp, count(grp) as cnt
    FROM (
        SELECT 1 as grp from Accounts where income < 20000
        UNION ALL
        SELECT 2 from Accounts where income BETWEEN 20000 AND 50000
        UNION ALL
        SELECT 3 from Accounts where income > 50000
    ) as grouped
    GROUP BY grp
)
,category_table as (
    SELECT "Low Salary" as category, 1 as rn
    UNION ALL
    SELECT "Average Salary", 2
    UNION ALL
    SELECT "High Salary", 3
)
SELECT c.category, COALESCE(g.cnt,0) as accounts_count
FROM category_table c LEFT JOIN grp_cnt g
on c.rn = g.grp

where로 잘라서 count(*)하고 이름만 붙이면 index활용이 잘 되어 빠를 것 같다고 생각해서
이렇게 적게 되었다.

다른 사람 쿼리 1

SELECT 
  c.category,
  IFNULL(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 'Average Salary'
      ELSE 'High Salary'
    END AS category,
    COUNT(*) AS accounts_count
  FROM Accounts
  GROUP BY category
) AS a ON c.category = a.category;

다른 사람 쿼리2

WITH all_cal as(
    SELECT "Low Salary" as catagory
    UNION ALL
    SELECT "Average Salary" as category
    UNION ALL 
    SELECT "High Salary" as category
), 


    order_call as (SELECT (
        CASE
            WHEN income < 20000 THEN "Low Salary"
            WHEN income >= 20000 and income <= 50000 THEN "Average Salary"
            WHEN income > 50000 THEN "High Salary"
            END
    ) as category, count(*) as accounts_count
    FROM Accounts
    GROUP BY category)

SELECT all_cal.catagory as category, IFNULL(order_call.accounts_count, 0) as accounts_count
FROM order_call
RIGHT JOIN all_cal ON order_call.category = all_cal.catagory

GPT 피셜 쿼리 평가

항목	당신의 쿼리	쿼리 1	쿼리 2
스캔 횟수	3회 (income 조건별)	1회	1회
인덱스 활용	✅ income 인덱스 range scan 가능	❌ CASE로 인해 미사용	❌ CASE로 인해 미사용
계산 복잡도	낮음 (조건 필터)	중간 (CASE 계산)	중간 (CASE 계산 + RIGHT JOIN)
카테고리 누락 처리	✅ LEFT JOIN	✅ LEFT JOIN	✅ RIGHT JOIN
대용량 데이터 적합도	⭐ 가장 우수	보통	낮음
가독성	중간	가장 우수	낮음