SELECT
    employee_name,
    salary,
    CASE
        WHEN salary BETWEEN 0 AND 500000
            THEN 'Entry Level'

        WHEN salary BETWEEN 500001 AND 1000000
            THEN 'Mid Level'

        ELSE 'Senior Level'
    END AS salary_band
FROM employees;

However, for numeric ranges, inequality conditions are often easier to maintain:

CASE
    WHEN salary < 500000 THEN 'Entry Level'
    WHEN salary < 1000000 THEN 'Mid Level'
    ELSE 'Senior Level'
END

Because the conditions are evaluated from top to bottom.

9️⃣ CASE for Customer Segmentation

Customer segmentation is a common analytics use case.

Suppose:

• total_spend represents total customer spending.

SELECT
    customer_id,
    customer_name,
    total_spend,
    CASE
        WHEN total_spend >= 100000 THEN 'VIP'
        WHEN total_spend >= 50000 THEN 'High Value'
        WHEN total_spend >= 10000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_segment
FROM customers;

This transforms raw spending into a business classification.

🔟 CASE for Order Size

Suppose you want to classify orders.

SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END AS order_size
FROM orders;

Result:

order_id | amount | order_size
101      | 12000  | Large
102      | 7000   | Medium
103      | 2500   | Small 

1️⃣1️⃣ CASE for Order Status

You can simplify several statuses into broader business categories.

SELECT
    order_id,
    order_status,
    CASE
        WHEN order_status = 'Completed'
            THEN 'Successful'

        WHEN order_status = 'Cancelled'
            THEN 'Unsuccessful'

        ELSE 'Pending'
    END AS business_status
FROM orders;

1️⃣2️⃣ CASE With Dates

You can classify orders based on when they were placed.

SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date < '2026-01-01'
            THEN 'Previous Year'
        ELSE 'Current Year'
    END AS order_period
FROM orders;

1️⃣3️⃣ CASE for Profitability

Suppose you have:

• selling_price

• cost_price

You can classify products based on profit.

SELECT
    product_name,
    selling_price,
    cost_price,
    selling_price - cost_price AS profit,

    CASE
        WHEN selling_price - cost_price >= 10000
            THEN 'Highly Profitable'

        WHEN selling_price - cost_price > 0
            THEN 'Profitable'

        ELSE 'Loss'
    END AS profitability
FROM products;

1️⃣4️⃣ CASE With Aggregate Functions

This is where CASE becomes extremely powerful.

Suppose you want to count completed orders.

SELECT
    SUM(
        CASE
            WHEN order_status = 'Completed'
            THEN 1
            ELSE 0
        END
    ) AS completed_orders
FROM orders;

Why does this work?

Each row becomes:

• Completed → 1

• Other → 0

Then SUM() adds them.

1️⃣5️⃣ Conditional Counting

You can calculate several metrics at once.

SELECT
    COUNT(*) AS total_orders,

    SUM(
        CASE
            WHEN order_status = 'Completed'
            THEN 1 ELSE 0
        END
    ) AS completed_orders,

    SUM(
        CASE
            WHEN order_status = 'Cancelled'
            THEN 1 ELSE 0
        END
    ) AS cancelled_orders
FROM orders;

This is called conditional aggregation.

It is one of the most useful SQL techniques for dashboard development.