CASE WHEN END

---

The CASE statement is SQL’s version of if-then-else logic. It allows you to create new column values based on specific conditions, making it one of the most powerful tools for data transformation.

---

Basic Syntax

The statement moves through conditions in order. Once a condition is met, it returns the result and stops looking. If no conditions are met, it returns whatever is in the ELSE clause.

CASE 
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE result_3
END AS new_column_name

---

Common Use Cases

Categorizing Numeric Data

You can use CASE to turn continuous numbers into descriptive categories (buckets).

SELECT 
    trip_id,
    fare_amount,
    CASE 
        WHEN fare_amount < 10 THEN 'Budget'
        WHEN fare_amount BETWEEN 10 AND 50 THEN 'Standard'
        ELSE 'Premium'
    END AS fare_category
FROM taxi_trips;

Transforming Codes into Labels

Raw data often uses cryptic codes. CASE makes this data human-readable.

SELECT 
    vendor_id,
    CASE 
        WHEN vendor_id = 1 THEN 'Creative Mobile'
        WHEN vendor_id = 2 THEN 'VeriFone Inc'
        ELSE 'Other'
    END AS vendor_name
FROM staging_table;

Simple "Flagging" (Boolean Logic)

You can create binary flags (0 or 1) which are very useful for machine learning features or simplified reporting.

SELECT 
    trip_id,
    CASE 
        WHEN passenger_count > 4 THEN 1 
        ELSE 0 
    END AS is_large_group
FROM trips;

---

Important Rules to Remember

• Order Matters: SQL stops at the first true condition. Always put your most specific conditions at the top.

• Data Types: Every result (the THEN and ELSE parts) must return the same data type. You cannot return a String in the first line and an Integer in the second.

• The ELSE is Optional: If you omit the ELSE and no conditions are met, the result will be NULL.

• End with END: You must always close the statement with the END keyword.

---

Using CASE with Aggregations

A "Pro" move in Data Engineering is putting a CASE statement inside an aggregate function like SUM or COUNT. This is often called Pivoting.

-- Counting how many "High Value" trips occurred per vendor
SELECT 
    vendor_id,
    SUM(CASE WHEN fare_amount > 100 THEN 1 ELSE 0 END) AS high_value_trip_count
FROM taxi_trips
GROUP BY vendor_id;

Summary Table

Part	Purpose	Requirement
CASE	Starts the logic block.	Required
WHEN	The condition to test (e.g., x > 5).	Required (at least one)
THEN	The value to return if WHEN is true.	Required
ELSE	The "fallback" if nothing is true.	Optional (defaults to NULL)
END	Closes the logic block.	Required

---