Formatted question description: https://leetcode.ca/all/1907.html

1907. Count Salary Categories

Level

Medium

Description

Table: Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id is the primary key for this table.
Each row contains information about the monthly income for one bank account.

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.

The query result format is in the following example.

Accounts table:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+

Result table:
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+

Solution

First, create t as the union of all categories Low Salary, Average Salary and High Salary. Next, convert the values of income into the corresponding categories. Finally, use left join to combine the query results and use ifnull to return 0 for categories with no accounts.

# Write your MySQL query statement below
select t.category, ifnull(a.cnt, 0) as accounts_count
    from (
        select 'Low Salary' category 
        union 
        select 'Average Salary' 
        union 
        select 'High Salary'
    ) as t
    left join (
        select
            case when income < 20000 then 'Low Salary'
                 when income > 50000 then 'High Salary'
                 else 'Average Salary' end
            as category,
            count(1) as cnt
        from Accounts
        group by 1
    ) as a
    on t.category = a.category;

All Problems

All Solutions