Categorize employees by salary: "High" for > 100000, "Medium" for > 60000, otherwise "Low".

Problem Statement

<p>Categorize <code>employees</code> by salary: "High" for > 100000, "Medium" for > 60000, otherwise "Low".</p>

Examples

Input: employees table: +----+---------+--------+ | id | name | salary | +----+---------+--------+ | 1 | Alice | 120000 | | 2 | Bob | 75000 | | 3 | Charlie | 50000 | | 4 | Dana | 90000 | +----+---------+--------+

Output: +---------+--------+-------------+ | name | salary | salary_band | +---------+--------+-------------+ | Alice | 120000 | High | | Bob | 75000 | Medium | | Charlie | 50000 | Low | | Dana | 90000 | Medium | +---------+--------+-------------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Use a conditional CASE WHEN ... THEN ... ELSE ... END expression to transform values or implement if-else logic. 💡 Hint 2: Each condition is evaluated in order; provide a sensible ELSE fallback if unhandled cases exist. 💡 Hint 3: Place the CASE expression in the SELECT list with an alias: SELECT CASE WHEN condition THEN val ELSE default END AS column_name FROM ...;

Editorial & Approach

Problem Overview & Intuition

To solve "Searched CASE (Multiple WHEN)", we query the relational database engine using declarative SQL. The goal is to categorize employees by salary: "high" for > 100000, "medium" for > 60000, otherwise "low". By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply row projections to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT name, salary, CASE WHEN salary > 100000 THEN 'High' WHEN salary > 60000 THEN 'Medium' ELSE 'Low' END AS salary_band FROM employees;

Complexity Analysis

Time Complexity O(N)

Space Complexity O(1)

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Searched CASE (Multiple WHEN)

Medium

Categorize employees by salary: "High" for > 100000, "Medium" for > 60000, otherwise "Low".

Example Scenarios

1Example 1

Input:

employees table

id	name	salary
1	Alice	120000
2	Bob	75000
3	Charlie	50000
4	Dana	90000

Output:

name	salary	salary_band
Alice	120000	High
Bob	75000	Medium
Charlie	50000	Low
Dana	90000	Medium

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.