# Introduction to BrewwQL

## What is BrewwQL?

We designed BrewwQL to help users simply and powerfully filter their data in Breww.

With a few simple operators, you can now easily create simple or complex filters to drill down deeper and more easily into your Breww data.

BrewwQL can seem daunting at first, especially if you're new to "query languages", but the doors it opens to both reporting and customisability in Breww make it worth reading on! If you've built reporting using SQL before, then you'll be right at home.

> **Note:**
> 💡 **Prefer a point-and-click interface?** Breww includes a [visual filter builder](https://breww.com/docs/brewwql-visual-filter-builder/) that lets you build BrewwQL filters without having to write any BrewwQL by hand. The rest of this guide covers the underlying BrewwQL language, which is useful to understand even if you usually use the visual builder.

## Examples

### Basic examples

To best understand BrewwQL, it's helpful to see some simple examples.

Let's say we wanted to see all of our customers whose delivery address was in London. In the customer filter form, we would type:

```sql
delivery_address_city = "London"
```

And that's it. The autocomplete will show every option as you go, making it really straightforward to put together queries and view available options.

If we want to drill down deeper, we can. From those customers, maybe we only want to see those whose average order value is greater than £250. For this we would chain two statements together with the `and` operator.

```sql
delivery_address_city = "London" and average_order_value > 250
```

Perhaps we are wondering which of those customers have lapsed, as we might want to contact them about reordering. So let's narrow the search for only customers who haven't ordered in the last 90 days.

```sql
delivery_address_city = "London" and average_order_value > 250 and last_order_date < "90 days ago"
```

Now let's say we are doing some admin, and we wanted to see all customers who we haven't entered a contact for yet. In the customer filter box, we would type:

```sql
contacts = None
```

### Advanced examples

BrewwQL allows us to traverse through attributes of what we are searching through. For example if we wanted to see customers who are part of a customer group with a billing address in London we would write:

```sql
customer_group.invoice_address_city = "London"
```

The `.` between `customer_group` and `invoice_address_city` is used in BrewwQL as a way of traversing through attributes of attributes.

We can also use brackets to add more complexity to our searches.

```sql
(delivery_address_city = "London" or average_order_value > 250) and last_order_date < "90 days ago"
```

The above statement will be analysed from left to right, and statements in brackets will be evaluated as a single attribute. So the query will retrieve all customers whose delivery address city is London OR who has an average order value greater than £250 ONLY if they also have a last order date more recent than 90 days ago.

We can also nest brackets within brackets. The below query will return either customers who are in London AND are Pubs, OR those whose average order value is greater than £250, as long as their last order date is more recent than 90 days ago.

```sql
((delivery_address_city = "London" and type.type = "Pub") or average_order_value > 250) and last_order_date < "90 days ago"
```

## Using the same attribute multiple times

This can be a little confusing at first. Each statement between operators, must be complete and be able to work on its own. This means that if you're joining multiple filters together with `and` or `or`, like `STATEMENT and STATEMENT`, both statements must be complete on their own.

We'll walk through an example - when looking at product filtering, you could filter the type of product to only include casks with:

```sql
type = "Cask"
```

Or you could filter kegs only with:

```sql
type = "Keg"
```

Now, if you wanted to include both casks and kegs, you might be tempted (and understandably so), to do this:

```sql
type = "Cask" and "Keg"
```

However, this is incorrect on two counts. The first being that each statement (each side of the `and`) must be a complete statement. Here, the `type = "Cask"` is perfect, but the second statement being just `"Keg"` is meaningless on its own, and so will not work. So, you can correct this to:

```sql
type = "Cask" and type = "Keg"
```

This is much closer (and hopefully explains what's meant by each statement being a complete statement when looked at on its own). However, in this particular case, this isn't what's needed. We're looking for both cask and keg products, but this will be checked against each individual product, and any single product cannot be both a cask and a keg. The above is filtering on products which are both casks and kegs (which is impossible), so what we really need to check is if the product is either a cask **or** the product is a keg. And that can be done with:

```sql
type = "Cask" or type = "Keg"
```

Success! The above would give the desired result of finding both cask products and keg products. If you wanted to make this a little neater (and maybe more readable), you could instead use the `in` operator to check if the product's type is in the list of options given. This would be done with:

```sql
type in ("Cask", "Keg")
```

This is certainly more readable if the number of options were to increase from two to four (or even more):

```sql
type in ("Cask", "Keg", "Service", "Guest beer")
```

Verses the more verbose:

```sql
type = "Cask" or type = "Keg" or type = "Service" or type = "Guest beer"
```

## Dates

BrewwQL allows you to filter on dates and has a powerful natural language understanding. For example, if you'd like all customers who have ordered since the 1st January 2021, you can simply enter:

```sql
last_order_date > "1st January 2021"
```

or:

```sql
last_order_date > "01/01/2021"
```

or even:

```sql
last_order_date > "1 Jan 2021"
```

BrewwQL also understands relative dates, so you can filter on the last 30 days with:

```sql
last_order_date > "30 days ago"
```

or filter on the last two months with:

```sql
last_order_date > "2 months ago"
```

Some other useful options for date filtering include the phrases:

```sql
last_order_date > "first of this month"
```

or:

```sql
last_order_date < "last of this month"
```

or:

```sql
last_order_date > "first of last month"
```

or:

```sql
last_order_date < "last of last month"
```

or:

```sql
last_order_date < "Monday last week"
```

or:

```sql
last_order_date < "Monday next week"
```

or:

```sql
last_order_date < "last Monday"
```

or:

```sql
last_order_date < "previous Monday"
```

or:

```sql
last_order_date < "next Tuesday"
```

or:

```sql
last_order_date < "this Wednesday"
```

You can also use broader "start of" / "end of" keywords for the current week, quarter or year:

```sql
last_order_date >= "start of this week"
```

```sql
last_order_date <= "end of this week"
```

```sql
last_order_date >= "start of this quarter"
```

```sql
last_order_date <= "end of this quarter"
```

```sql
last_order_date >= "start of this year"
```

```sql
last_order_date <= "end of this year"
```

> **Note:**
> **A date is greater** `>` **than another if it is more recent, and less** `<` **than another if it is older.**

You can think of this as if someone started counting at the beginning of time. The number they had counted to last week is less than the number they have counted to right now.

To filter on a range of dates, you can use the same field twice:

```sql
last_order_date > "first of last month" and last_order_date  <= "last of last month"
```

### Date and time fields

For fields that include a time (like `created_at` or `invoice_date_time`), you can combine a date with a time keyword such as `end of day`, `start of day`, `noon` or `midnight`:

```sql
created_at >= "Yesterday start of day" and created_at <= "Yesterday end of day"
```

```sql
created_at >= "Today noon"
```

You can also still specify an exact time like `13:23` or `1:23pm` at the end of the date value.

### Date ranges

BrewwQL date filtering is always based on a specific date, rather than date ranges. For this reason, to create a date range filter, you need to add two filters. For example:

```sql
last_order_date > "01/01/2025" and last_order_date  <= "07/01/2025"
```

As date ranges cannot be used in a single filter, the following will **not work**:

```sql
last_order_date = "This week"
```

But this **will work** to achieve a filter of "This week":

```sql
last_order_date >= "This Monday" and last_order_date  <= "This Sunday"
```

> **Note:**
> 💡 **Tip:** The [visual filter builder](https://breww.com/docs/brewwql-visual-filter-builder/) includes a **between** operator for dates that generates the two-filter form for you automatically, along with a dropdown of common presets like "Start of this week" and "Start of this year".

## Numbers

Numeric fields accept standard numbers like `100` or `1.5`. If your Breww account is set to a locale that uses a comma as the decimal separator (such as German or French), you can also enter decimals in that form by quoting the value:

```sql
average_order_value > "1,5"
```

## Case-sensitivity

Do capitals matter in my searches? **No**, a search for:

```sql
delivery_address_city = "London"
```

and

```sql
delivery_address_city = "london"
```

will return the same results.

## Operator reference

| Operator | Meaning                  | Example                                                 | Verbose                                                                     |
| -------- | ------------------------ | ------------------------------------------------------- | --------------------------------------------------------------------------- |
| `=`      | Equal to                 | `name = "The Shop"`                                     | Where the name is equal to "The Shop"                                       |
| `!=`     | Not equal to             | `name != "The Shop"`                                    | Where the name is not equal to "The Shop"                                   |
| `~`      | Contains                 | `name ~ "The"`                                          | Where the name contains "The"                                               |
| `!~`     | Does not contain         | `name !~ "The"`                                         | Where the name does not contain "The"                                       |
| `>`      | Greater than             | `average_order_value > 100`                             | Where the average order value is greater than 100                           |
| `>=`     | Greater than or equal to | `average_order_value >= 100`                            | Where the average order value is greater than or equal to 100               |
| `<`      | Less than                | `average_order_value < 100`                             | Where the average order value is less than 100                              |
| `<=`     | Less than or equal to    | `average_order_value <= 100`                            | Where the average order value is less than or equal to 100                  |
| `in`     | In                       | `name in ("The Shop", "The Pub", "The Restaurant")`     | Where the name is either "The Shop" or "The Pub" or "The Restaurant"        |
| `not in` | Not in                   | `name not in ("The Shop", "The Pub", "The Restaurant")` | Where the name is not "The Shop" and not "The Pub" and not "The Restaurant" |

***

## Building filters without writing BrewwQL

If you'd rather not write BrewwQL by hand, Breww includes a [visual filter builder](https://breww.com/docs/brewwql-visual-filter-builder/) that lets you pick fields, operators and values from dropdowns - including a **between** operator for date and number ranges, and a dropdown of common date presets like "Start of this week" and "Start of this year". See the [visual filter builder guide](https://breww.com/docs/brewwql-visual-filter-builder/) for a full walkthrough.

## More in this guide

- [Using the visual filter builder](https://breww.com/docs/brewwql-visual-filter-builder.md)