---
title: Use filters in SQL queries
slug: n2oa7409
canonical_url: https://docs.coveo.com/en/n2oa7409/
collection: coveo-analytics
source_format: adoc
---
# Use filters in SQL queries
When creating [report cards](https://docs.coveo.com/en/267/) in either the [Snowflake reader account](https://docs.coveo.com/en/l9e90297#whats-a-reader-account) or through [Data Share](https://docs.coveo.com/en/m1rf0146/), you will likely want to apply filters on dimensions and metrics to fine-tune your results, the same way you would when creating cards on the [**Reports**](https://platform.cloud.coveo.com/admin/#/orgid/usage/reports/) ([platform-ca](https://platform-ca.cloud.coveo.com/admin/#/orgid/usage/reports/) | [platform-eu](https://platform-eu.cloud.coveo.com/admin/#/orgid/usage/reports/) | [platform-au](https://platform-au.cloud.coveo.com/admin/#/orgid/usage/reports/)) page of the [Coveo Administration Console](https://docs.coveo.com/en/183/).

![Report with filters section highlighted | Coveo Platform](https://docs.coveo.com/en/assets/images/coveo-platform/report-filters.png)

When building your SQL query, add a [date range filter](#date-range-filter), followed by [dimension and metric filters](#operators-for-dimension-and-metric-filters), using the applicable SQL snippets.

## Date range filter

The date range filter is the only filter applied directly to the event tables before any other operation.
It can be applied to the table directly using subqueries or a common table expression (`CTE`) via the SQL `WITH` clause.

**Example**

You want to recreate the following report card that has a filter on the context `website`.

![Metric card showing visits by country with date range | Coveo Platform](https://docs.coveo.com/en/assets/images/coveo-analytics/metric-card-date-range.png)

In the corresponding SQL query, the date range is applied to each event table:

```sql
WITH "cte_searches" AS (
    SELECT "search_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000'::TIMESTAMP_TZ AND '2023-01-12 23:59:59.999'::TIMESTAMP_TZ
), "cte_clicks" AS (
    SELECT "search_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000'::TIMESTAMP_TZ AND '2023-01-12 23:59:59.999'::TIMESTAMP_TZ
), "cte_customs" AS (
    SELECT "last_search_id" AS "search_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."custom_events_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000'::TIMESTAMP_TZ AND '2023-01-12 23:59:59.999'::TIMESTAMP_TZ
)
SELECT
    COALESCE("cte_searches"."origin_country", "cte_clicks"."origin_country", "cte_customs"."origin_country") AS "Country"
    , COUNT(DISTINCT COALESCE("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_customs"."visit_id")) AS "Country - Visits"
FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
        FULL OUTER JOIN "cte_customs" on "cte_searches"."search_id" = "cte_customs"."search_id"
WHERE COALESCE("cte_searches"."website","cte_clicks"."website","cte_customs"."website") in ('engineering','Engineering')
GROUP BY "Country"
ORDER BY "Country - Visits" DESC;
```

## Operators for dimension and metric filters

The following reference table contains the operators with their corresponding [API symbols](https://docs.coveo.com/en/2727/) and SQL snippets that can be applied between the selected dimension or metric and the value.

[%header,cols="~,~,~"]
|===
|API symbol
|Operator
|SQL snippet

|`=`
|Is

a|
```sql
(<DIMENSION_EXPRESSION>) IN ( (<VALUE>), (<VALUE>), … );
```

|`!=`
|Is not
a|
```sql
NOT((<DIMENSION EXPRESSION>) IN (<VALUE>), (<VALUE>), … );
```

|`=@`
|Contains (case insensitive `IS` operator)

a|
```sql
<DIMENSION EXPRESSION> ILIKE '%<value>%';
```

|`!@`
|Does not contain (case insensitive `IS NOT` operator)
a|
```sql
NOT(<DIMENSION EXPRESSION> ILIKE '%<value>%');
```

|`==`
|Is equal to
a|
```sql
<METRIC EXPRESSION> = <VALUE>;
```

|`!=`
|Is not equal to
a|
```sql
<METRIC EXPRESSION> <> <VALUE>;
```

|`<`
|Is lower than

a|
```sql
<METRIC EXPRESSION> < <VALUE>;
```

|`<=`
|Is lower than or equal to

a|
```sql
<METRIC EXPRESSION> <= <VALUE>;
```

|`>`
|Is greater than

a|
```sql
<METRIC EXPRESSION> > <VALUE>;
```

|`>=`
|Is greater than or equal to

a|
```sql
<METRIC EXPRESSION> >= <VALUE>;
```
|===

> **Note**
>
> For more information on the `ILIKE` function and how to use an escape character, see the [Snowflake reference documentation](https://docs.snowflake.com/en/sql-reference/functions/ilike.html).

### Blank and null values

`NULL` (n/a) is a special value and can't be used over a filter operator like other values since it always returns `FALSE`.
Therefore, the `IS NULL` or `IS NOT NULL` conditional expression must be used.
For more information, see the pertaining [SQL reference provided by Snowflake](https://docs.snowflake.com/en/sql-reference/functions/is-null.html).

A blank value is an empty string with zero length or no characters.
It's handled the same way as a null value (over the filter interface), since selecting an empty string value over a list is counter-intuitive.
However, it's still a valid value to filter on as any other string over a condition.

> **Notes**
>
> * When both `NULL` and blank expressions are selected, the `OR` operator is used:
>  
> ```sql
(
    <DIMENSION EXPRESSION> IS NULL
    OR <DIMENSION EXPRESSION> = ''
)
```
> 
> * When multiple values are selected, the `OR` operator is used:
>  
> ```sql
(
    <DIMENSION EXPRESSION> IS NULL
	OR <DIMENSION EXPRESSION> = ''
	OR <DIMENSION EXPRESSION> in (<VALUE>, <VALUE>, …)
)
```
> 
> * When a negation expression is used (for example, `IS NOT`), the `NOT` applies to the overall expression:
>  
> ```sql
not(
	<DIMENSION EXPRESSION> IS NULL
    OR <DIMENSION EXPRESSION> = ''
    OR <DIMENSION EXPRESSION> IN (<VALUE>, <VALUE>, …)
)
```