Count Salary Categories SQL MySQL solution with case and CTE
Count Salary Categories SQL MySQL solution with case and CTE
Common table expression in leetcode problems.
Challenge: Counting Salary Categories
Difficulty: Medium
Data:
A table named Accounts stores monthly income for bank accounts:
account_id (int): Unique identifier for each account (primary key).
income (int): The monthly income for the corresponding account.
Objective:
Categorize accounts based on their income and count the number of accounts within each category:
"Low Salary": Income strictly less than $20,000.
"Average Salary": Income inclusively between $20,000 and $50,000.
"High Salary": Income strictly greater than $50,000.
Output:
A table with three columns:
category (varchar): The salary category ("Low Salary", "Average Salary", or "High Salary").
accounts_count (int): The number of accounts within that category.
Explanation:
We need to classify each account into a salary category based on its income and then count the occurrences within each category. Here are two common approaches:
Approach 1: Using CASE Expressions:
Use a CASE expression within the main query to categorize each account based on income thresholds.
Group the results by category and use a count aggregation function to calculate the number of accounts in each category.
Approach 2: Using WHERE Clauses:
Perform separate queries for each category using WHERE clauses to filter accounts based on the specific income ranges.
Union all the individual category counts into a single result table.
Both approaches achieve the desired outcome. The choice might depend on personal preference or database system functionalities.
Important Note:
The prompt specifies that the result table must include all three categories, even if there are no accounts in a particular category. Make sure the chosen approach handles this requirement by returning 0 for categories with no matching accounts.
SQL query categorizes income levels into three groups: 'Low Salary', 'Average Salary', and 'High Salary', and then counts the number of accounts in each category from the Accounts table. Finally, it ensures that all possible categories are included in the results, even if there are no accounts in some categories.
Here's a detailed breakdown of each part of the query:
Common Table Expressions (CTEs)
The query uses two CTEs, C and T.
CTE C
This CTE defines a table C with a single column category that includes three rows: 'Low Salary', 'Average Salary', and 'High Salary'.
UNION is used to combine these three rows into one table.
CTE T
This CTE processes the Accounts table to categorize the income of each account.
The CASE statement determines the category based on the income value:
income lt 20000 'Low Salary'
income lt 50000 AND income gt 20000 'Average Salary'
income lt 50000 - 'High Salary'
The result is grouped by category, and the COUNT 1 function counts the number of accounts in each category.
This CTE produces a table T with columns category and account_count.
Main Query
The main query performs a LEFT JOIN between the C and T tables on the category column.
LEFT JOIN ensures that all categories from C are included in the final result, even if there are no matching categories in T.
IFNULL(account_count, 0) replaces NULL values in account_count with 0, ensuring that categories with no accounts are represented with a count of 0.
Explanation of the Final Output
The final result will have the category column from C and the account_count column from T.
If a category from C has no corresponding rows in T (i.e., no accounts fall into that category), account_count will be 0.
This ensures that all possible categories ('Low Salary', 'Average Salary', 'High Salary') are listed in the output, providing a complete view of the categorization even if some categories have no associated accounts.
Watch video Count Salary Categories SQL MySQL solution with case and CTE online, duration hours minute second in high quality that is uploaded to the channel CodeWis Technologies by Nuhman Paramban 22 May 2024. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 16 times and liked it 1 visitors.
