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
SQL
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
SQL
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
SQL
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
Pandas
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]
})