Optimizing SQL Queries with CASE WHEN Technique
Let’s explore how to use the CASE WHEN statement to implement conditional logic and combine it with window functions like SUM() and COUNT() to handle complex aggregations.
👋 Hey! This is Manisha Arora from PrepVector. Welcome to the Tech Growth Series, a newsletter that aims to bridge the gap between academic knowledge and the practical aspects of data science. My goal is to simplify complicated data concepts, share my perspectives on the latest trends, and share my learnings from building and leading data teams.
Hello SQL Enthusiasts,
Welcome to another edition of SQL Insights! After exploring SQL joins and the SQL Query Execution Order, it's time to take your SQL skills to the next level by mastering the CASE WHEN statement. This powerful tool not only helps with conditional logic but also optimizes complex queries, allowing you to handle multiple conditions in a clean and efficient way.
These posts are part of the larger Learn Data Science with Me initiative, a collaboration between Manisha Arora, Sai Kumar Bysani, and Siddarth R. Together, we’re creating a community-driven learning experience designed for professionals who want to deepen their data science expertise.
What is the Learn Data Science with Me Initiative?
I’ve set a personal goal to upskill 1,000 data professionals in 3 months, and I’m thrilled to have you join this journey. It’s all about building practical skills, engaging with thought leaders, and tackling real-world challenges together.
This initiative will be an immersive learning experience focused on industry learnings, skills, and use cases. Here’s what you can expect:
✅ Coding Challenges: Practical SQL, Python, and ML problems to sharpen your problem-solving skills
✅ Paper Reading Sessions: Discussions on cutting-edge research papers and blogs to bridge theory and practice
✅ Speaker Sessions: Interactive Q&A and deep dives into trending industry topics
✅ AMA Discussions: Open forums for real-time learning and insights
The 15-day SQL Challenge, launching in March, is the first structured challenge of the series! If you enjoyed this week’s aggregation-focused content, get ready for what’s next. The challenge will cover everything from writing efficient queries to mastering advanced SQL optimizations, ensuring you build a strong foundation in SQL.
🧩 What is CASE WHEN?
The CASE WHEN statement in SQL allows you to implement conditional logic within your queries, letting you return different values based on conditions. It’s similar to an IF-ELSE statement in programming.
🔑 Syntax of CASE WHEN
There are two basic ways to use CASE WHEN:
Simple CASE – This version evaluates one expression and compares it to multiple values.
SELECT
order_id,
CASE order_status
WHEN 'Pending' THEN 'Order Received'
WHEN 'Shipped' THEN 'On the Way'
WHEN 'Delivered' THEN 'Order Complete'
ELSE 'Unknown Status'
END AS order_status_description
FROM orders;In this example, the query checks the value of order_status and assigns a more descriptive value based on the condition.
Searched CASE – This allows for more complex conditions with Boolean expressions.
SELECT
order_id,
CASE
WHEN order_amount > 100 THEN 'High Value'
WHEN order_amount BETWEEN 50 AND 100 THEN 'Medium Value'
ELSE 'Low Value'
END AS order_value_category
FROM orders;💡 When and Why to Use CASE WHEN
Simplifying Multiple Conditions:
Instead of writing multiple AND / OR conditions in your WHERE clause, you can combine them into a single CASE WHEN statement, improving readability.
For example, categorizing orders based on their value without writing multiple conditions in the WHERE clause:
SELECT
order_id,
CASE
WHEN order_amount > 1000 THEN 'High Value'
WHEN order_amount BETWEEN 500 AND 1000 THEN 'Medium Value'
ELSE 'Low Value'
END AS order_category
FROM orders;This query categorizes the orders using CASE WHEN based on the order amount, improving clarity and readability.
Replacing Complex IF Logic:
CASE WHEN can replace complicated IF-ELSE logic, allowing you to build more elegant queries.
For example, You can use CASE WHEN to evaluate conditions in your data for categorization, such as grouping customers based on their loyalty. Instead of running multiple queries for each membership level, you can handle this within a single query.
SELECT
customer_id,
CASE
WHEN loyalty_points > 100 THEN 'Gold Member'
WHEN loyalty_points BETWEEN 50 AND 100 THEN 'Silver Member'
ELSE 'Bronze Member'
END AS membership_status
FROM customers;Combining with Window Functions:
One of the most powerful ways to use CASE WHEN is in combination with window functions like SUM(), COUNT(), AVG(), etc. When you use CASE WHEN inside a window function, you can apply conditional logic across partitions of data while keeping the query efficient.
For example - Le’s say we have a table of sales transactions, and we want to calculate the total sales for each employee, but we only want to count sales greater than $100. We can achieve this using CASE WHEN in conjunction with the SUM() window function.
SELECT
employee_id,
transaction_id,
sale_amount,
SUM(CASE WHEN sale_amount > 100 THEN sale_amount ELSE 0 END)
OVER (PARTITION BY employee_id ORDER BY transaction_id) AS high_value_sales
FROM sales_transactions;Let’s take another example. We have a table of orders, and we want to count the number of "completed" orders for each customer using a window function. We'll use CASE WHEN to count only completed orders.
SELECT
customer_id,
order_id,
order_status,
COUNT(CASE WHEN order_status = 'Completed' THEN 1 END)
OVER (PARTITION BY customer_id) AS completed_orders
FROM orders;Here’s another example: We have sales data for each product category along with the region. We want to calculate the sales from the North and South regions separately.
SELECT
product_category,
SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS north_sales,
SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS south_sales
FROM sales_data
GROUP BY product_category;🔥Exciting Announcement: The 15-Day SQL Challenge!
Are you ready to test your SQL skills and take them to the next level? We’re excited to announce our 15-Day SQL Challenge, which kicks off in March! Here’s how it works:
One question per day for 15 days: You’ll receive a daily SQL problem, ranging from beginner to advanced topics, to help you sharpen your skills.
Leaderboard: Track your progress and compare your performance with others on the leaderboard!
Rewards: Those who complete the challenge will receive exciting rewards and recognition.
This is the perfect opportunity to challenge yourself, compete with your peers, and grow your SQL knowledge!
Don’t miss out! The challenge is going to be a blast. Let’s get ready to level up your SQL skills together!
🚀 Join Our Data Science Community!
Become a part of the Learn Data Science with Me community and connect with fellow professionals on the same journey. Get access to exclusive content, participate in interactive discussions, and stay updated on challenges, speaker events, and more. Join us today and accelerate your learning with a supportive network of data science enthusiasts!
That’s it for this week’s edition of SQL Insights!
Next edition, we’ll dive into window functions, which will take your SQL skills to the next level. Stay tuned, and happy querying!