Abstract / Summary

Count how many salary ranges fall into given buckets based on lower and upper bounds.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 65% (400k+ submissions).

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The provided SQL script classifies accounts into three salary categories-Low Salary, Average Salary, and High Salary-and returns the number of accounts that fall into each bucket. It does this by executing three separate SELECT statements, each counting rows that satisfy a specific income range, and then concatenating the results with UNION ALL. The final result set contains two columns: a textual label for the category and the corresponding count of accounts.

Key Steps

Defining the Low Salary bucket

The first SELECT isolates accounts whose income is strictly less than 20 000. It labels the bucket as “Low Salary” and uses COUNT(*) to compute the total number of matching rows.

SELECT "Low Salary" AS category, COUNT(*) AS accounts_count
  FROM Accounts
 WHERE income < 20000

Defining the Average Salary bucket

The second SELECT captures accounts with income between 20 000 and 50 000 inclusive. The BETWEEN operator includes both boundary values, ensuring that incomes exactly equal to 20 000 or 50 000 are counted in this bucket. The bucket is labeled “Average Salary”.

SELECT "Average Salary" AS category, COUNT(*) AS accounts_count
  FROM Accounts
 WHERE income BETWEEN 20000 AND 50000

Defining the High Salary bucket

The third SELECT targets accounts whose income exceeds 50 000. It labels the bucket as “High Salary” and counts the rows that satisfy the condition.

SELECT "High Salary" AS category, COUNT(*) AS accounts_count
  FROM Accounts
 WHERE income > 50000

Combining the buckets

The three bucket queries are combined using UNION ALL. This operator appends the result sets without attempting to eliminate duplicate rows, which is appropriate because each bucket produces a distinct category label and the counts are independent.

SELECT "Low Salary" AS category, COUNT(*) AS accounts_count
  FROM Accounts
 WHERE income < 20000
 UNION ALL
SELECT "Average Salary" AS category, COUNT(*) AS accounts_count
  FROM Accounts
 WHERE income BETWEEN 20000 AND 50000
 UNION ALL
SELECT "High Salary" AS category, COUNT(*) AS accounts_count
  FROM Accounts
 WHERE income > 50000

Edge Cases Handled

The script implicitly handles several edge conditions. First, the use of <, BETWEEN … AND …, and > creates mutually exclusive ranges, guaranteeing that each account is counted in exactly one bucket. The inclusive nature of BETWEEN ensures that incomes exactly equal to the lower and upper bounds (20 000 and 50 000) are placed in the “Average Salary” bucket, preventing overlap with the low or high categories. Second, rows where income is NULL are automatically excluded from all three WHERE clauses, because any comparison with NULL yields an unknown result, and the row does not satisfy the predicate. This behavior means that accounts lacking an income value do not affect the bucket counts.

Performance Optimizations Observed

The query leverages UNION ALL rather than UNION. By avoiding the duplicate-elimination step, the database engine can stream the three result sets directly, reducing CPU overhead and memory consumption. Each bucket performs a simple COUNT(*) over a filtered scan, which allows the optimizer to use index-only access if an index exists on the income column. An index on income would enable the engine to locate the start and end points for each range efficiently, turning each count into a range scan rather than a full table scan. Additionally, because the three counts are independent, the planner may execute them in parallel on systems that support parallel query execution, further improving throughput.

Overall, the script presents a clear, straightforward approach to categorizing and counting accounts by salary range, employing set operations and filtering techniques that align well with typical relational database optimizations.

Imagine you have a giant toy chest filled with cards, and each card has a person's name and how much money they have written on it. This computer code is like a helper who looks through the whole chest to organize the cards into three special piles.

First, the helper picks up all the cards that show a small amount of money and puts them into a pile labeled Low Salary. Then, the helper picks up all the cards that show a medium amount of money and puts those into a pile labeled Average Salary. Finally, the helper takes all the cards with a large amount of money and puts them into a pile labeled High Salary. After sorting everyone, the helper counts how many cards ended up in each pile so that we know exactly how many people are in each group.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

Time: linear
As a rule of thumb, the maximum input size for one second execution time is

• factorial: up to 10 elements
• exponential: up to 20 elements
• polynomial: a few thousand elements
• loglinear: up to 100,000 elements
• linear: a few million elements
• logarithmic: a billion elements
• constant: practically unlimited

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	Progiez		C++	Java	Python
github	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality