Using CASE WHEN as IF statements in SQL

You’ve most likely used the IF function in Excel or Google Sheets to define what should happen if certain conditions are fulfilled. In SQL the CASE statement functions the same way. The function is split into a few parts:

• CASE WHEN This describes the logic you want to test. In this logic, you can use the same functions as you did earlier when filtering the rows: =, !=, <, >, LIKE, and NOT
• THEN This part describes what should happen if the condition is fulfilled
• ELSE This describes what happens if the condition is not fulfilled
• END This indicates that the statement is ended
  

For example, say you’d like to create a new column that checks whether the product is a bread product. This could be done in a query like this:

1select
2    id
3  , order_id
4  , product
5  , quantity
6  , price
7  , case
8        when product like '%Bread%' then 'Yes'
9        else 'No'
10    end as Bread
11from
12    {{raw.e_commerce_sample.webshop_order_line}}

‍

Notice here that even though the CASE WHEN statement spans over several lines in the query, it’s only creating one new column in the table. Try doing it on your own. You should get a result like this:

You can combine as many WHEN and THEN statements as you like. Just be sure to put them before the ELSE. For example, say that you want the cookie dough from the result to be included in the Bread category, too. You can add that to the query like this:

1select
2    id
3  , order_id
4  , product
5  , quantity
6  , price
7  , case
8        when product like '%Bread%' then 'Yes'
9        when product like '%Dough%' then 'Yes'
10        else 'No'
11    end as Bread_and_dough
12from
13    {{raw.e_commerce_sample.webshop_order_line}}

‍

As you see here, CASE is only written once, and then WHEN and THEN is repeated for each test. Remember that your column names are not supposed to include spaces. The result should look like this:

Exercise 7: Adding a unit to the quantity if the product is bread or cheese

Say your e-commerce business wants to upsell cheese and bread. You want to see how much you would have sold historically if you could add one more unit to the quantity of each order line for bread and cheese products. Of course, this would need to be a more sophisticated analysis to be reliable, but for now, try to write a query that calculates a new total value that adds one more quantity if the product is bread or cheese. If you run your query, it should give a result like this:

In SQL there are often many means to an end, so you’ll usually be able to get the required result by writing different variations of the same query. A simple way to solve the exercise above would be to write a query like this:

1select
2    id
3  , order_id
4  , product
5  , quantity
6  , price
7  , case
8        when product like '%Bread%' then (quantity + 1) * price
9        when product like '%Cheese%' then (quantity + 1) * price
10        else quantity * price
11    end as Bread_and_Cheese_Upselling
12from
13    {{raw.e_commerce_sample.webshop_order_line}}

‍