SQL Quest › SQL Interview Questions › Conditional Logic

Bonus Tier with Cross-Conditions

MediumFreeQuerying BasicsConditional Logic

Comp planning at scale: bonus tier depends on both performance and salary band, so the same rating can map to different bonuses depending on what the employee already earns. Tiers, in priority order (the first match wins):

1. 'Probation' — performance_rating IS NULL
2. 'Stretch Bonus' — performance_rating >= 4.5 AND salary < 80000 (rewarding rising stars who are still on lower comp)
3. 'Standard Bonus' — performance_rating >= 3.5 (regardless of salary)
4. 'Watch List' — performance_rating < 3.0
5. 'No Bonus' — everyone else

Show name, department, performance_rating, salary, bonus_tier. Order by performance_rating descending (NULLs last), then name ascending.

The trap: branch order matters. If 'Standard Bonus' (>= 3.5) comes before 'Stretch Bonus' (>= 4.5 AND salary < 80000), every high performer below 80k gets caught by Standard first and the Stretch logic never runs. Also: NULL performance fails every numeric comparison silently — handle it explicitly with the IS NULL branch first, otherwise those rows wrongly fall through to 'No Bonus'.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

employees

emp_id	name	department	position	salary	hire_date	manager_id	performance_rating
1	Alice Johnson	Engineering	Senior Developer	95000	2019-03-15	5	4.5
2	Bob Smith	Engineering	Developer	75000	2020-06-01	1	3.8
3	Carol Williams	Marketing	Marketing Manager	85000	2018-09-20	NULL	4.2

Expected output: Alice → Stretch Bonus

Hint

Put the IS NULL check FIRST. Then the most-specific compound condition (rating >= 4.5 AND salary < 80000). Then broader conditions. Each WHEN needs THEN — forgetting THEN is a syntax error and the most common mistake on this pattern.

Concepts

SELECT CASE AND Filter

Practise the topic: SQL practice questions · CASE WHEN practice

In these company practice sets

Plaid

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Bonus Tier with Cross-Conditions

Schema

Hint

Concepts

In these company practice sets

Related questions