CASE

🎬 Scenario: Categorizing Amazon Orders

Your manager at Amazon, Sophia, wants a report on customer orders, but instead of just showing raw order statuses, she wants them categorized:

Can you create a report where orders are labeled as ‘Completed’ for delivered orders, ‘In Progress’ for shipped or pending orders, and ‘Canceled’ for canceled orders?

Your task is to use CASE to classify orders into meaningful categories for better reporting. Use the amazon_orders and amazon_customers tables to address the questions.

amazon_orders table:

amazon_customers table:

1. Understanding CASE Statements

A CASE statement allows you to apply conditional logic within SQL queries.

Syntax

1SELECT column1,  
2       CASE  
3           WHEN condition1 THEN result1  
4           WHEN condition2 THEN result2  
5           ELSE default_result  
6       END AS new_column  
7FROM table_name;

• Evaluates each condition in order.
• Returns the first matching condition.
• If no conditions match, the ELSE result is used (optional).

2. Using CASE to Categorize Order Status

Sophia wants a simplified order status report.

Example Query: Categorizing Amazon Orders

1SELECT order_id, customer_id, total_price, status,  
2       CASE  
3           WHEN status = 'Delivered' THEN 'Completed'  
4           WHEN status IN ('Pending', 'Shipped') THEN 'In Progress'  
5           WHEN status = 'Canceled' THEN 'Canceled'  
6           ELSE 'Unknown'  
7       END AS order_category  
8FROM amazon_orders  
9ORDER BY order_category;

What’s Happening?

• The CASE statement groups order statuses into three categories:
  • Completed → Delivered orders
  • In Progress → Pending or shipped orders
  • Canceled → Orders marked as canceled
• The result is a cleaner report that makes order tracking easier.

3. Using CASE to Categorize Customers Based on Spending

Sophia also wants to classify customers based on their total spending.

Example Query: Classifying Customers by Spending

1SELECT customer_id, name, total_spent,  
2       CASE  
3           WHEN total_spent > 5000 THEN 'High Spender'  
4           WHEN total_spent BETWEEN 1000 AND 5000 THEN 'Moderate Spender'  
5           ELSE 'Low Spender'  
6       END AS spending_category  
7FROM amazon_customers  
8ORDER BY total_spent DESC;

What’s Happening?

• The CASE statement assigns customers a category based on their total spending.
• This helps Amazon identify high-value customers for personalized promotions.

✍️ SQL Exercises

Exercise 1: Categorizing Amazon Orders

Sophia needs a simplified order report showing whether orders are Completed, In Progress, or Canceled.

Filter condition:

• Retrieve order ID, customer ID, total price, and status.
• Add a new columnorder_category:
  • 'Completed' for Delivered orders.
  • 'In Progress' for Shipped or Pending orders.
  • 'Canceled' for Canceled orders.
• Sort by order_category in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Categorizing Customers by Spending

Sophia also wants to categorize Amazon customers based on their spending behavior.

Filter condition:

• Retrieve customer ID, name, and total spent.
• Add a new columnspending_category:
  • 'High Spender' for customers with total spent greater than $5000.
  • 'Moderate Spender' for total spent between $1000 and $5000.
  • 'Low Spender' for total spent less than $1000.
• Sort by total_spent in descending order.

Write an SQL query to return the requested data.