Use filters in SQL queries

This is for:

Business Analyst System Administrator

When creating report cards in either the Snowflake reader account or through Data Share, 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 (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console.

report filters

When building your SQL query, we recommend adding a date range filter, followed by 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-date-range

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

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 and SQL snippets that can be applied between the selected dimension or metric and the value.

API symbol Operator SQL snippet

=

Is

 
(<DIMENSION_EXPRESSION>) IN ( (<VALUE>), (<VALUE>), … );

!=

Is not

 
NOT((<DIMENSION EXPRESSION>) IN (<VALUE>), (<VALUE>), … );

=@

Contains (case insensitive IS operator)

 
<DIMENSION EXPRESSION> ILIKE '%<value>%';

!@

Does not contain (case insensitive IS NOT operator)

 
NOT(<DIMENSION EXPRESSION> ILIKE '%<value>%');

==

Is equal to

 
<METRIC EXPRESSION> = <VALUE>;

!=

Is not equal to

 
<METRIC EXPRESSION> <> <VALUE>;

<

Is lower than

 
<METRIC EXPRESSION> < <VALUE>;

⇐

Is lower than or equal to

 
<METRIC EXPRESSION> <= <VALUE>;

>

Is greater than

 
<METRIC EXPRESSION> > <VALUE>;

>=

Is greater than or equal to

 
<METRIC EXPRESSION> >= <VALUE>;
Note

For more information on the ILIKE function and how to use an escape character, see the Snowflake reference documentation.

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.

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:

    (
    <DIMENSION EXPRESSION> IS NULL
    OR <DIMENSION EXPRESSION> = ''
)

  • When multiple values are selected, the OR operator is used:

    (
    <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:

    not(
	<DIMENSION EXPRESSION> IS NULL
    OR <DIMENSION EXPRESSION> = ''
    OR <DIMENSION EXPRESSION> IN (<VALUE>, <VALUE>, …)
)