adb125f84b1.js","269","static/chunks/269-5f9235f83b944543.js","222","static/chunks/222-e37d1f81e0d6d2ef.js","934","static/chunks/app/cs/problems/%5Bslug%5D/page-935d3f20891d8cfc.js"],"default"] 18:I[2647,["874","static/chunks/874-64ff3adb125f84b1.js","269","static/chunks/269-5f9235f83b944543.js","222","static/chunks/222-e37d1f81e0d6d2ef.js","934","static/chunks/app/cs/problems/%5Bslug%5D/page-935d3f20891d8cfc.js"],"default"] 1a:I[7912,["874","static/chunks/874-64ff3adb125f84b1.js","269","static/chunks/269-5f9235f83b944543.js","222","static/chunks/222-e37d1f81e0d6d2ef.js","934","static/chunks/app/cs/problems/%5Bslug%5D/page-935d3f20891d8cfc.js"],"default"] 19:T104a,

Problem

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.

Examples

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.

Solution

Method 1 - CTE and Cases

Code

[{"language":"SQL","code":"WITH CTE AS(\nSELECT account_id,\n    CASE \n        WHEN income < 20000 THEN \"Low Salary\"\n        WHEN income > 50000 THEN \"High Salary\"\n        ELSE \"Average Salary\"\n    END AS category\nFROM Accounts),\n\nCTE_C AS(        \nSELECT \"Low Salary\" AS Category\nUNION \nSELECT \"Average Salary\" AS Category\nUNION \nSELECT \"High Salary\" AS Category\n) #need a table of category to make left join \n#>>> prevent  those category without value in CTE from disappearing \n\nSELECT CTE_C.Category, COUNT(account_id) AS  accounts_count\nFROM CTE_C\nLEFT JOIN CTE ON CTE_C.Category = CTE.Category\nGROUP BY CTE_C.Category","lang":"sql"}]

Method 2 - Case and Union

Code

[{"language":"SQL","code":"SELECT \"Low Salary\" AS Category,\nSUM(CASE WHEN income<20000 THEN 1 ELSE 0 end) AS accounts_count\nFROM Accounts\nUNION\nSELECT  \"Average Salary\" Category,\nSUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) AS accounts_count\nFROM Accounts\nUNION\nSELECT \"High Salary\" category,\nSUM(CASE WHEN income>50000 THEN 1 ELSE 0 END) AS accounts_count\nFROM Accounts","lang":"sql"}]

Method 3 - Union and Count

Code

[{"language":"SQL","code":"SELECT 'Low Salary' as category, COUNT(account_id) as accounts_count\nFROM Accounts\nWHERE

Related Tags