TECH **SQL for Data Analysis: CASE Statements for Conditional Logic**

SQL for Data Analysis: CASE Statements for Conditional Logic

Hyper-Practical, Zero-Fluff Study Guide

1. What This Is & Why It Matters

What it is:
A CASE statement in SQL is your if-then-else for databases. It lets you apply conditional logic directly in your queries—no need to pull data into Python or Excel just to categorize, flag, or transform values.

Why it matters in production:
- Avoids post-processing: Instead of exporting data to clean it in Excel, you do it in SQL, saving time and reducing errors.
- Dynamic reporting: Need to bucket customers into "High," "Medium," or "Low" spenders? CASE does it in one query.
- Legacy system fixes: Inherited a messy database where "status" is stored as 1, 2, or 3? CASE translates it to human-readable labels.
- Performance: Filtering or aggregating with CASE is often faster than multiple WHERE clauses or subqueries.

Real-world scenario:
You’re analyzing e-commerce data. The "payment_status" column uses cryptic codes (0 = Pending, 1 = Paid, 2 = Refunded). Your boss wants a report showing only refunded orders from Q3 2023, grouped by product category. Without CASE, you’d: 1. Export the data.
2. Manually recode the statuses in Excel.
3. Filter and pivot.
With CASE, you do it in one query—no exports, no errors.

2. Core Concepts & Components

• CASE expression
  A conditional statement that evaluates conditions and returns a value when the first condition is met.
  Production insight: Use it to standardize messy data (e.g., converting M/F to Male/Female) without altering the source table.

• Simple CASE
  Compares a single expression to multiple values (like a switch statement).
  sql CASE payment_status
WHEN 0 THEN 'Pending'
WHEN 1 THEN 'Paid'
WHEN 2 THEN 'Refunded'
ELSE 'Unknown' END Production insight: Great for mapping codes to labels (e.g., 1 = Active, 0 = Inactive).

• Searched CASE
  Evaluates multiple conditions (like if-then-else).
  sql CASE
WHEN revenue > 1000 THEN 'High'
WHEN revenue > 500 THEN 'Medium'
ELSE 'Low' END Production insight: Use for dynamic bucketing (e.g., customer segmentation).

• ELSE clause
  Default value if no conditions are met.
  Production insight: Always include ELSE—missing values can break dashboards or reports.

• END keyword
  Closes the CASE statement.
  Production insight: Forgetting END is a top syntax error—your query will fail silently.

• Aggregation with CASE
  Use CASE inside SUM(), COUNT(), or AVG() to conditionally aggregate.
  sql SELECT
product_category,
SUM(CASE WHEN payment_status = 2 THEN 1 ELSE 0 END) AS refund_count FROM orders GROUP BY product_category; Production insight: This is 10x faster than filtering with WHERE and running multiple queries.

• CASE in ORDER BY
  Sort results based on conditional logic.
  sql ORDER BY
CASE WHEN priority = 'High' THEN 1 ELSE 2 END; Production insight: Useful for custom sorting (e.g., "Show high-priority tickets first").

• CASE in UPDATE
  Conditionally update rows.
  sql UPDATE users SET status = CASE
WHEN last_login < '2023-01-01' THEN 'Inactive'
ELSE 'Active' END; Production insight: Backup your data first—a misplaced CASE can update the wrong rows.

3. Step-by-Step Hands-On: Writing Your First CASE Query

Prerequisites

• A SQL environment (e.g., PostgreSQL, MySQL, BigQuery, or SQL Server).
• A table with sample data. If you don’t have one, run this to create a test table: ```sql CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  product_category VARCHAR(50),
  revenue DECIMAL(10,2),
  payment_status INT -- 0=Pending, 1=Paid, 2=Refunded );

INSERT INTO orders VALUES
(1, 101, '2023-07-15', 'Electronics', 1200.00, 1),
(2, 102, '2023-08-20', 'Clothing', 450.00, 2),
(3, 103, '2023-09-05', 'Electronics', 800.00, 0),
(4, 104, '2023-10-10', 'Home', 300.00, 1),
(5, 105, '2023-11-12', 'Clothing', 600.00, 2); ```

Task: Generate a Report with Human-Readable Statuses

Goal: Write a query that: 1. Translates payment_status codes to labels.
2. Flags high-revenue orders (> $500).
3. Filters for refunded orders in Q3 2023.

Step 1: Translate payment_status with CASE

SELECT
  order_id,
  customer_id,
  order_date,
  product_category,
  revenue,
  CASE payment_status
    WHEN 0 THEN 'Pending'
    WHEN 1 THEN 'Paid'
    WHEN 2 THEN 'Refunded'
    ELSE 'Unknown'
  END AS payment_status_label
FROM orders;

Expected Output:
| order_id | customer_id | order_date | product_category | revenue | payment_status_label | |----------|-------------|-------------|------------------|---------|----------------------| | 1 | 101 | 2023-07-15 | Electronics | 1200.00 | Paid | | 2 | 102 | 2023-08-20 | Clothing | 450.00 | Refunded | | 3 | 103 | 2023-09-05 | Electronics | 800.00 | Pending | | 4 | 104 | 2023-10-10 | Home | 300.00 | Paid | | 5 | 105 | 2023-11-12 | Clothing | 600.00 | Refunded |

Step 2: Add a Revenue Flag

SELECT
  order_id,
  customer_id,
  order_date,
  product_category,
  revenue,
  CASE payment_status
    WHEN 0 THEN 'Pending'
    WHEN 1 THEN 'Paid'
    WHEN 2 THEN 'Refunded'
    ELSE 'Unknown'
  END AS payment_status_label,
  CASE
    WHEN revenue > 500 THEN 'High'
    ELSE 'Low'
  END AS revenue_flag
FROM orders;

New Column: revenue_flag (High/Low).

Step 3: Filter for Refunded Orders in Q3 2023

SELECT
  order_id,
  customer_id,
  order_date,
  product_category,
  revenue,
  CASE payment_status
    WHEN 0 THEN 'Pending'
    WHEN 1 THEN 'Paid'
    WHEN 2 THEN 'Refunded'
    ELSE 'Unknown'
  END AS payment_status_label,
  CASE
    WHEN revenue > 500 THEN 'High'
    ELSE 'Low'
  END AS revenue_flag
FROM orders
WHERE
  payment_status = 2
  AND order_date BETWEEN '2023-07-01' AND '2023-09-30';

Expected Output:
| order_id | customer_id | order_date | product_category | revenue | payment_status_label | revenue_flag | |----------|-------------|-------------|------------------|---------|----------------------|--------------| | 2 | 102 | 2023-08-20 | Clothing | 450.00 | Refunded | Low |

Step 4: Aggregate Refunds by Category

SELECT
  product_category,
  COUNT(*) AS total_refunds,
  SUM(CASE WHEN revenue > 500 THEN 1 ELSE 0 END) AS high_revenue_refunds
FROM orders
WHERE
  payment_status = 2
  AND order_date BETWEEN '2023-07-01' AND '2023-09-30'
GROUP BY product_category;

Expected Output:
| product_category | total_refunds | high_revenue_refunds | |------------------|---------------|----------------------| | Clothing | 1 | 0 |

4. ? Production-Ready Best Practices

Performance

• Avoid CASE in WHERE clauses—it can prevent index usage. Filter first, then apply CASE.
  ❌ Bad: sql WHERE CASE WHEN revenue > 500 THEN 1 ELSE 0 END = 1 ✅ Good: sql WHERE revenue > 500

• Use CASE in SELECT or GROUP BY—it’s optimized for these contexts.

Readability

• Indent nested CASE statements for clarity: sql CASE
WHEN condition1 THEN
CASE
WHEN subcondition THEN 'A'
ELSE 'B'
END
ELSE 'C' END

• Name CASE columns clearly (e.g., revenue_flag instead of flag).

Maintainability

• Document complex CASE logic with comments: sql -- payment_status: 0=Pending, 1=Paid, 2=Refunded CASE payment_status
WHEN 0 THEN 'Pending'
WHEN 1 THEN 'Paid'
WHEN 2 THEN 'Refunded' END

• Test edge cases (e.g., NULL values, unexpected codes).

Security

• Sanitize inputs if CASE logic depends on user-provided values (e.g., in dynamic SQL).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Basic translation:
   "Write a query to convert 1/0 to Active/Inactive."
   ✅ Answer:
   sql
CASE status
WHEN 1 THEN 'Active'
WHEN 0 THEN 'Inactive'
END

2. Conditional aggregation:
   "Count orders where revenue > 1000 and status = 'Paid'."
   ✅ Answer:
   sql
SELECT
COUNT(CASE WHEN revenue > 1000 AND status = 'Paid' THEN 1 END) AS high_value_paid_orders
FROM orders;

3. Bucketing:
   "Categorize customers into 'Bronze', 'Silver', or 'Gold' based on spend."
   ✅ Answer:
   sql
CASE
WHEN total_spend > 1000 THEN 'Gold'
WHEN total_spend > 500 THEN 'Silver'
ELSE 'Bronze'
END

⚠️ Trap Distinctions

• CASE vs IF:
• CASE is an expression (returns a value).
• IF is a statement (executes logic, not available in all SQL dialects).
• CASE in GROUP BY:
  You can group by a CASE expression, but it’s often clearer to use a CTE first.
• Short-circuiting:
  CASE stops evaluating after the first true condition. Order matters!

7. ? Hands-On Challenge

Challenge:
You have a table employees with columns salary and department. Write a query to: 1. Categorize employees as "Low" (<50k), "Medium" (50k–100k), or "High" (>100k) earners.
2. Count how many employees are in each category per department.

Solution:

SELECT
  department,
  COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_earners,
  COUNT(CASE WHEN salary BETWEEN 50000 AND 100000 THEN 1 END) AS medium_earners,
  COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners
FROM employees
GROUP BY department;

Why it works:
- COUNT(CASE WHEN ... THEN 1 END) counts only rows that meet the condition.
- GROUP BY department ensures counts are per department.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL CASE Documentation – Official docs with advanced examples.
2. SQLZoo CASE Tutorial – Interactive exercises.
3. SQL for Data Analysis (O’Reilly) – Chapter 5 covers CASE in depth.
4. BigQuery CASE Examples – For cloud-based SQL.