Retrieve a card summary with SQL

This is for:

Business Analyst System Administrator

This article provides examples of how to calculate summaries of report cards using SQL queries. For some cards within a Coveo Administration Console dashboard , Coveo Usage Analytics (Coveo UA) computes a summary of the card. To do so, it uses the same query used to create the card, but without projecting the grouping columns.

Metric card summary

By using SQL queries, you can compute the summary at different levels of complexity based on your requirements.

Note

These queries demonstrate one way to calculate the card summary. While there are other methods (for example, window functions), we have presented them in simple building blocks that can be used in different tools.

Calculate a simple summary

To calculate a simple summary, you must first run the query to retrieve the card’s data points and produce the chart.

ALTER SESSION SET timezone = 'America/New_York';
SET START_DATE = '2023-05-19 00:00:00.000'::TIMESTAMP_TZ;
SET END_DATE = '2023-06-18 23:59:59.999'::TIMESTAMP_TZ;

-- Query to retrieve the card's data points to produce the chart.
WITH "cte_searches" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN $START_DATE AND $END_DATE
), "cte_clicks" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN $START_DATE AND $END_DATE
), "cte_custom_events" AS (
    SELECT "datetime", "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 $START_DATE AND $END_DATE
)
SELECT DISTINCT
    date_trunc('day', convert_timezone('UTC', 'America/New_York', coalesce("cte_searches"."datetime", "cte_clicks"."datetime", "cte_custom_events"."datetime"))) AS "date"
    , count(distinct "cte_searches"."search_id" ) as "Search Event Count"
    , count(distinct "cte_clicks"."click_id") as "Click Event Count"
    , count(distinct coalesce("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_custom_events"."visit_id")) as "Unique Visits"
FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
        FULL OUTER JOIN "cte_custom_events" ON "cte_searches"."search_id" = "cte_custom_events"."search_id"
WHERE coalesce("cte_searches"."website","cte_clicks"."website", "cte_custom_events"."website") in ('engineering','Engineering')
GROUP BY "date"
ORDER BY "date" DESC;

Then, run the query with GROUP BY ROLLUP to retrieve the summary.

WITH "cte_searches" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN $START_DATE AND $END_DATE
), "cte_clicks" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN $START_DATE AND $END_DATE
), "cte_custom_events" AS (
    SELECT "datetime", "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 $START_DATE AND $END_DATE
)
SELECT
    date_trunc('day', convert_timezone('UTC', 'America/New_York', coalesce("cte_searches"."datetime", "cte_clicks"."datetime", "cte_custom_events"."datetime"))) AS "date"
    , count(distinct "cte_searches"."search_id" ) as "Search Event Count"
    , count(distinct "cte_clicks"."click_id") as "Click Event Count"
    , count(distinct coalesce("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_custom_events"."visit_id")) as "Unique Visits"
FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
        FULL OUTER JOIN "cte_custom_events" ON "cte_searches"."search_id" = "cte_custom_events"."search_id"
WHERE coalesce("cte_searches"."website","cte_clicks"."website", "cte_custom_events"."website") in ('engineering','Engineering')
GROUP BY ROLLUP ("date")
ORDER BY "date" DESC;

Results in:

Summary card SQL results
Note

The summary is located in row 1 with the NULL data value.

Calculate a complex summary

If a more complex summary is required, a second query can be added.

-- The query is the same one to generate the card result, removing all groupings (and any ordering by those groups).
WITH "cte_searches" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
), "cte_clicks" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
), "cte_custom_events" AS (
    SELECT "datetime", "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 '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
)
SELECT
    count(distinct "cte_searches"."search_id" ) as "Search Event Count"
    , count(distinct "cte_clicks"."click_id") as "Click Event Count"
    , count(distinct coalesce("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_custom_events"."visit_id")) as "Unique Visits"
FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
        FULL OUTER JOIN "cte_custom_events" ON "cte_searches"."search_id" = "cte_custom_events"."search_id"
WHERE coalesce("cte_searches"."website","cte_clicks"."website", "cte_custom_events"."website") in ('engineering','Engineering');

Results in:

complex-summary-sql-results

Different aggregation levels

If a summary requires the card data at different levels of aggregation, it’s possible to combine them.

WITH "cte_searches" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
), "cte_clicks" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
), "cte_custom_events" AS (
    SELECT "datetime", "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 '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
),  "base_card_data" as (
    SELECT DISTINCT
        date_trunc('day', convert_timezone('UTC', 'America/New_York', coalesce("cte_searches"."datetime", "cte_clicks"."datetime", "cte_custom_events"."datetime"))) AS "date"
        , "cte_searches"."search_id"
        , "cte_clicks"."click_id"
        , coalesce("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_custom_events"."visit_id") as "visit_id"
    FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
            FULL OUTER JOIN "cte_custom_events" ON "cte_searches"."search_id" = "cte_custom_events"."search_id"
    WHERE coalesce("cte_searches"."website","cte_clicks"."website", "cte_custom_events"."website") in ('engineering','Engineering')
), "card_data" AS (
    SELECT
        "date"
        ,count(distinct "search_id" ) as "Search Event Count"
        , count(distinct "click_id") as "Click Event Count"
        , count(distinct "visit_id") as "Unique Visits"
    FROM "base_card_data"
    GROUP BY "date"
)
SELECT     (select count(distinct "search_id") from "base_card_data" ) as "Search Event Count"
        , (select avg("Search Event Count") from "card_data" ) as "Search Avg"
        , (select max("Search Event Count") from "card_data" ) as "Search Peak"
        , (select count(distinct "click_id") from "base_card_data" ) as "Click Event Count"
        , (select avg("Click Event Count") from "card_data" ) as "Click Avg"
        , (select max("Click Event Count") from "card_data" ) as "Click Peak"
        , (select count(distinct "visit_id") from "base_card_data" ) as "Unique Visits"
        , (select avg("Unique Visits") from "card_data" ) as "Unique Visits Avg"
        , (select max("Unique Visits") from "card_data" ) as "Unique Visits Peak"

Results in:

Card aggregation results

All in one query

WITH "cte_searches" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
), "cte_clicks" AS (
    SELECT "datetime", "search_id", "click_id", "origin_country", "visit_id", "custom_datas":"c_context_website"::string AS "website", "click_rank"
    FROM "COVEO"."ua"."clicks_shared"
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
), "cte_custom_events" AS (
    SELECT "datetime", "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 '2023-05-19 00:00:00.000'::TIMESTAMP_TZ AND '2023-06-18 23:59:59.999'::TIMESTAMP_TZ
),  "base_card_data" as (
    SELECT DISTINCT
        date_trunc('day', convert_timezone('UTC', 'America/New_York', coalesce("cte_searches"."datetime", "cte_clicks"."datetime", "cte_custom_events"."datetime"))) AS "date"
        , "cte_searches"."search_id"
        , "cte_clicks"."click_id"
        , coalesce("cte_searches"."visit_id", "cte_clicks"."visit_id", "cte_custom_events"."visit_id") as "visit_id"
    FROM "cte_searches" LEFT OUTER JOIN "cte_clicks" ON "cte_searches"."search_id" = "cte_clicks"."search_id"
            FULL OUTER JOIN "cte_custom_events" ON "cte_searches"."search_id" = "cte_custom_events"."search_id"
    WHERE coalesce("cte_searches"."website","cte_clicks"."website", "cte_custom_events"."website") in ('engineering','Engineering')
), "card_data" AS (
    SELECT
        "date"
        ,count(distinct "search_id" ) as "Search Event Count"
        , count(distinct "click_id") as "Click Event Count"
        , count(distinct "visit_id") as "Unique Visits"
    FROM "base_card_data"
    GROUP BY "date"
)
SELECT  null as "date"
        , (select count(distinct "search_id") from "base_card_data" ) as "Search Event Count"
        , (select avg("Search Event Count") from "card_data" ) as "Search Avg"
        , (select max("Search Event Count") from "card_data" ) as "Search Peak"
        , (select count(distinct "click_id") from "base_card_data" ) as "Click Event Count"
        , (select avg("Click Event Count") from "card_data" ) as "Click Avg"
        , (select max("Click Event Count") from "card_data" ) as "Click Peak"
        , (select count(distinct "visit_id") from "base_card_data" ) as "Unique Visits"
        , (select avg("Unique Visits") from "card_data" ) as "Unique Visits Avg"
        , (select max("Unique Visits") from "card_data" ) as "Unique Visits Peak"
UNION ALL
SELECT  "date"
        , "Search Event Count"
        , null as "Search Avg"
        , null as "Search Peak"
        , "Click Event Count"
        , null as "Click Avg"
        , null as "Click Peak"
        , "Unique Visits"
        , null as "Unique Visits Avg"
        , null as "Unique Visits Peak"
FROM "card_data"
ORDER BY "date" DESC;

Results in:

All in one SQL results