Problem

Table: Accounts

1
2
3
4
5
6

+-------------+------+
| 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:

1
2
3
4
5
6
7
8

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

Output:

1
2
3
4
5
6
7

+----------------+----------------+
| 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

WITH CTE AS(
SELECT account_id,
    CASE 
        WHEN income < 20000 THEN "Low Salary"
        WHEN income > 50000 THEN "High Salary"
        ELSE "Average Salary"
    END AS category
FROM Accounts),

CTE_C AS(        
SELECT "Low Salary" AS Category
UNION 
SELECT "Average Salary" AS Category
UNION 
SELECT "High Salary" AS Category
) #need a table of category to make left join 
#>>> prevent  those category without value in CTE from disappearing 

SELECT CTE_C.Category, COUNT(account_id) AS  accounts_count
FROM CTE_C
LEFT JOIN CTE ON CTE_C.Category = CTE.Category
GROUP BY CTE_C.Category

Method 2 - Case and Union

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT "Low Salary" AS Category,
SUM(CASE WHEN income<20000 THEN 1 ELSE 0 end) AS accounts_count
FROM Accounts
UNION
SELECT  "Average Salary" Category,
SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts
UNION
SELECT "High Salary" category,
SUM(CASE WHEN income>50000 THEN 1 ELSE 0 END) AS accounts_count
FROM Accounts

Complexity

  • Time: O(NNNXXXNNN)
  • Space: O(NNNXXX)

Method 3 - Union and Count

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT 'Low Salary' as category, COUNT(account_id) as accounts_count
FROM Accounts
WHERE income<20000
UNION
SELECT 'Average Salary' as category, COUNT(*)
FROM Accounts
WHERE income>=20000 AND income<=50000
UNION
SELECT 'High Salary' as category, COUNT(*)
FROM Accounts
WHERE income>50000
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
  low_count = (accounts['income'] < 20000).sum()
  average_count = ((accounts['income'] >= 20000) & (accounts['income'] <= 50000)).sum()
  high_count = (accounts['income'] > 50000).sum()
  return pd.DataFrame({
    'category': ['Low Salary', 'Average Salary', 'High Salary'],
    'accounts_count': [low_count, average_count, high_count]
})