Count Salary Categories Problem


Description

LeetCode Problem 1907.

Table: Accounts

1
2
3
4
5
6
7
8
+-------------+------+
| 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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              |
+----------------+----------------+

Low Salary: Account 2.
Average Salary: No accounts.
High Salary: Accounts 3, 6, and 8.


MySQL Solution

1
2
3
4
5
select "Low Salary" as category, count(*) as accounts_count from Accounts where income < 20000
union 
select "Average Salary" as category, count(*) as accounts_count from Accounts where 20000 <= income and income <= 50000
union
select "High Salary" as category, count(*) as accounts_count from Accounts where 50000 < income




Related Posts

Leetcodify Friends Recommendations Problem

LeetCode 1917. Write an SQL query to recommend friends to...

Count Salary Categories Problem

LeetCode 1907. Write an SQL query to report the number...

The Latest Login in 2020 Problem

LeetCode 1890. Write an SQL query to report the latest...

Suspicious Bank Accounts Problem

LeetCode 1843. Write an SQL query to report the IDs...

Page Recommendations II Problem

LeetCode 1892. Write an SQL query to find all the...

Orders With Maximum Quantity Above Average Problem

LeetCode 1867. Write an SQL query to find the order_id...