Tutorial

Optimize SQL Queries with CASE Expressions in Unexpected Ways

It is time to take your SQL skills from average to extraordinary with Nested Case Expressions

Jess Loiterton, Pexels

It’s the unexpected things that change our lives. — Shonda Rhimes

I’m black and I grew up in a neighborhood with gangs. Most friends I grew up with are dead, in prison, on drugs, or just stuck. They consistently ranked the schools I attended in the bottom tier. Now I’m writing technical articles to a bunch of data geeks like myself who find life to be an unfolding mystery of data problems to be solved. That’s unexpected.

If you’ve read my other tutorials, you realize by now I see coding as an artistic endeavor; an adventure in frustration. Each code line has its own hue, beauty and texture. It reflects an intimate choice of the coder layered in hours of torturous examination or reflection. The canvas on which the coder paints her solution is broad, reflecting her perspective and experience. I eschew all pretense of technical sophistication when writing these tutorials. That’s unexpected. I don’t use overly complex terms or examples. I love to knowledge share so I try to use language that is accessible. So, like the other tutorials, this tutorial adds a tool to the coders’ repository.

A repository is a treasure chest of valuables stored in abundance. The Case Expression should be part of the SQL coders treasure chest. So, I’m writing a series on using Case Expressions in unexpected ways. This is part 2 in the series:

• Part 1: CASE Expressions and Window Functions
• Part 2: Nested CASE Expressions
• Part 3: Subqueries and CASE Expressions (Coming Soon)

These approaches can move you from average to extraordinary. So, buckle your seat belts and enjoy the journey.

Nested Case Expression

The Nested CASE Expession extends the power and precision of the CASE Expression. The CASE Expression has the following characteristics:

• The CASE Expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the value.
• CASE allows you to display an alternative value to the actual value in a column.¹
• A common use of CASE is to replace codes or abbreviations with more readable values.¹
• A CASE can build columns with calculated metrics.

Alternatively, the Nested CASE expression has the following characteristics:

• Nested CASE incorporates all the characteristics of the CASE.
• With Nested CASE, you can split the data into subgroups or segments. A differing logic can apply to each subgroup.

Nested CASE Expression Syntax

Author, Case Syntax

In the Nested CASE expression, the outer statement executes until satisfying a condition. Once the condition is met, the inner CASE expression executes and returns a single result. If no condition is met in the outer statement, CASE expression returns the value in the ELSE statement.

The Use Case

Our fictitious company has four divisions located across the United States States. The company wants to test different pricing strategies in each division. The Campaign Manager requests a data file with the following requirements for each division :

• East: Customers receive the product offer of $19.99 if the customer is Female lives in New York or New Jersey. Other customers remaining in the North receive the standard offer of $29.99.
• South: Customers receive the product offer of $25.99 if the customer is Male lives in Florida or Georgia. Other customers remaining in the North receive the standard offer of $35.99.
• West: Customers receive the product offer of $29.99 if the customer is Female lives in California or Nevada. Other customers remaining in the North receive the standard offer of $39.99.
• Midwest: West: Customers receive the product offer of $21.99 if the customer is male lives in Iowa or South Dakota. Other customers remaining in the North receive the standard offer of $31.99.

The Data: Hands-On

Please access the data and SQL code on Github or Google Drive to follow along. I built the solution using a Postgres database. So, you’ll need to upload the data to a Postgres database. As an alternative, you can change the syntax and use another database, such as MySQL, Redshift, Oracle or SQL Server.

The Solution

When I was inexperienced, I would look at these types of requirements and hack my way to a solution. I can imagine splitting the divisions using individual WHERE clauses and then merging the data using a series of UNIONqueries. Finally, concluding with CREATE and UPDATE table statements to arrive at a solution. I would be proud I spent 2 days fighting through the “challenges”. As a great SQL warrior, I slayed another foe. I felt my efforts were heroic and worthy of applause. In retrospect, they were neither. It was just inexperience that lacked imagination or creativity. The better solution, shown below, is using a Nested CASE Expression.

Author, Nested Case Expression Solution

The Nested CASE Expression segments the data into divisions. Once the segmentation is complete, we apply the individual business requirements based on state and gender. The result is a promotion_price column completed in one or two hours. No struggle, sleepless nights or hassle. That’s unexpected.

Finally, the path to excellent SQL skills includes mastering CASE Expressions along with Common Table Expressions (CTE’s), Window Functions and Derived tables. I will share more about these in upcoming tutorials. Keep learning, growing and sharing. That’s expected.

If you liked this article, here is another article in the series you may enjoy:

SQL Nested Window Functions

Shared Inspiration: With each lesson shared, I am reminded of Mr. Julius Duncan. I remember the first day I met him. Wearing a white coat and walking toward our door, I thought he was a doctor. Instead, he was a gardener and the greatest man I ever met. To help single mothers, he took on work for less than cost. He generously shared his time, faith, and resources. It was common for him to pay for college school books or help in other ways. When he passed, the line to pay honor lasted for more than an hour. He was an everyday hero. A light in the darkness. Keep sharing and inspiring others to be greater than they could have imagined.

[1] Chris Fehily, Visual Start Guide, SQL (2008).