LeetCode 코딩 테스트 - Count Salary Categories(LV.Medium)

 

 

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.

 

# 쿼리를 작성하는 목표, 확인할 지표 : 범위에 맞게 salary의 카테고리 부여 / income
# 쿼리 계산 방법 : 1. if 문으로 조건에 따라 category부여 -> 2. null일 경우 0 -> 3. union all로 병합
# 데이터의 기간 : x
# 사용할 테이블 : accounts
# JOIN KEY : x
# 데이터 특징 : x
SELECT
    'Low Salary' AS category,
    # 1 & 2
    COALESCE(COUNT(IF(income < 20000, 1, NULL)), 0) AS accounts_count
FROM accounts
# 3
UNION ALL
SELECT
    'Average Salary' AS category,
    # 1 & 2
    COALESCE(COUNT(IF(income BETWEEN 20000 AND 50000, 1, NULL)), 0) AS accounts_count
FROM accounts
# 3
UNION ALL
SELECT
    'High Salary' AS category,
    # 1 & 2
    COALESCE(COUNT(IF(income > 50000, 1, NULL)), 0) AS accounts_count
FROM accounts