AND / OR: Filtering your data with several conditions

For slightly more advanced analysis, filtering based on one condition is often not enough. You can combine several conditions in your WHERE statement by using the statements AND and OR. For example, you might want to only see fulfilled orders and only to a shipping address in certain countries — let’s say Sweden, Norway, and Australia. This information is located in the Order table, so you’ll need to go back to it. These conditions could be defined in a query like this:

1select
2    *
3from
4    {{raw.e_commerce_sample.webshop_order}}
5where
6    shipping_address_country = 'Sweden'
7    and fulfillment_status = 'Shipped'
8    or shipping_address_country = 'Australia'
9    and fulfillment_status = 'Shipped'
10    or shipping_address_country = 'Norway'
11    and fulfillment_status = 'Shipped'

‍

While SQL code, in general, is not case sensitive, the values you use as conditions encapsulated by single quotation marks are sensitive to upper and lower letters. Make sure you write the conditions exactly as written in the query above. If you run this query on your own, the result should look like this:

The AND statement only returns values if both conditions are satisfied, while the OR statement returns values if just one of the conditions is satisfied. As you see in the query above, you can combine these statements as you please, and in that way create your own advanced filter.

Exercise 3: Find all items where the price is between 50 and 55

Continue with the webshop_order_line table from the exercise before. This time you want to find the items where the price is between 50 and 55. See if you can write a query to get to this result. 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
7from
8    {{raw.e_commerce_sample.webshop_order_line}}
9where
10    price > 50
11    and price < 55

_‍