Abstract / Summary

Given employee ratings and their positions, compute each employee's special bonus based on how many coworkers have lower ratings.

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 57% (600k+ submissions).

Typical patterns to solve this problem:

• Database

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 statement retrieves a list of employees together with a calculated bonus value. The bonus is derived from a conditional expression that checks two attributes of each row: whether the employee identifier is odd and whether the employee’s name does not begin with the letter “M”. The result set is sorted by the identifier, which typically aligns with the primary-key ordering of the underlying table.

Key Steps

The query can be broken down into three logical phases: selection, conditional computation, and ordering.

The selection phase isolates the columns needed for the output.

SELECT employee_id, IF(employee_id % 2 = 1 AND name NOT LIKE 'M%', salary, 0) AS bonus

The conditional computation phase evaluates the business rule that defines a bonus. The IF function receives three arguments: a Boolean condition, the value to return when the condition is true, and the value to return when it is false. The condition combines two predicates with a logical AND.

IF(employee_id % 2 = 1 AND name NOT LIKE 'M%', salary, 0)

The ordering phase arranges the rows in ascending order of the identifier, which often leverages an index on employee_id.

ORDER BY employee_id

Edge Cases Handled

When the identifier is even, the modulo operation yields zero, causing the first predicate to be false; the entire condition short-circuits and the bonus is set to zero.

If the name column contains a NULL value, the NOT LIKE predicate evaluates to NULL, which is treated as false in the logical AND. Consequently, the bonus defaults to zero for those rows.

Names that start with the letter “M” in any case (e.g., “Mike”, “maria”) are excluded because the pattern 'M%' matches only an uppercase “M”. Rows where the name begins with a different character, including lowercase “m”, satisfy the NOT LIKE predicate and may receive a bonus if the identifier is also odd.

When the salary column is NULL and the condition is true, the IF function returns NULL for the bonus, preserving the original data semantics rather than substituting an arbitrary numeric value.

Performance Optimizations Observed

The query avoids joins, subqueries, and aggregation, keeping the execution plan straightforward. The modulo operation (employee_id % 2) is a simple arithmetic calculation that can be evaluated row-by-row without additional I/O.

The ORDER BY employee_id clause is likely to be satisfied by an existing index on the primary key, eliminating the need for a separate sorting step. This index-driven ordering reduces disk reads and memory consumption during result generation.

Using the built-in IF function instead of a CASE expression reduces parsing overhead and yields a compact execution path. The conditional logic is evaluated only once per row, and the early short-circuit behavior of the logical AND prevents unnecessary evaluation of the second predicate when the first predicate fails.

Overall, the statement is written to take advantage of index support and minimal computational work, resulting in efficient data retrieval even on large employee tables.

Imagine there is a big magical list of names and numbers that tells everyone who works at a company how much money they earn. This code acts like a very fast reading robot that looks at the list one person at a time to decide who receives a special surprise gift. The robot looks for two things. First, it checks the employee number to see if it is an odd number, which is like counting by twos and ending up with one left over. Second, it peeks at the name to make sure it does not start with the letter M. If both things are true, the robot marks down their salary as the gift amount. If even one of those things is not true, the robot writes down a zero instead because there is no gift for them today. Finally, the robot makes sure the final list shows everyone in the right order starting from the smallest number at the top.

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

not analyzed yet

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	everthis	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
everthis	SQL
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