---
title: Retrieve a card summary with SQL
slug: n6je0405
canonical_url: https://docs.coveo.com/en/n6je0405/
collection: coveo-analytics
source_format: adoc
---
# Retrieve a card summary with SQL

This article provides examples of how to calculate summaries of [report cards](https://docs.coveo.com/en/267.md) using SQL queries.
For some cards within a [Coveo Administration Console](https://docs.coveo.com/en/183.md) [dashboard](https://docs.coveo.com/en/256.md) , [Coveo Analytics](https://docs.coveo.com/en/182.md) 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 showing Click Event Count Search Event Count and Unique Visits | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/metric-card-summary.png)

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](https://docs.snowflake.com/en/sql-reference/functions-analytic)), we've 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.

```sql
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.

```sql
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:

![SQL query results showing dates and event counts | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/summary-card-sql-results.png)

> **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.

```sql
-- 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:

![SQL results showing search event click event and unique visit counts | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/complex-summary-sql-results.png)

### Different aggregation levels

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

```sql
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:

![Snowflake query result table for card aggregation | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/card-aggregation-sql-results.png)

### All in one query

```sql
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 | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/all-in-one-results.png)