---
title: Retrieve trend data
slug: n77d8110
canonical_url: https://docs.coveo.com/en/n77d8110/
collection: coveo-analytics
source_format: adoc
---
# Retrieve trend data
This article provides an example of how to calculate trend data between different report periods as you would in the [Coveo Administration Console](https://docs.coveo.com/en/183/).
For instance, when adding a metric [report card](https://docs.coveo.com/en/267/) to a [dashboard](https://docs.coveo.com/en/256/), you can view trend data between the current period and the previous one by selecting the **Display trend data** option when creating a card.

![Display trend data | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/display-trend-data.png)

When selected, the trend value appears next to the metric value in the card as a percentage with an up or down arrow, respectively indicating an increasing or a decreasing trend.

![Metric card trend | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/metric-card-trend.png)

The period comparison is determined by the date ranges selected with the **Report Period** panel (the previous period is selected by default).

![Report period | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/report-period-date-picker.png)

By using an SQL query, you can compute the trend data between different date ranges depending on your requirements.

> **Note**
>
> The trend data shows the ratio between two summary values.
> For more information on how to compute a card summary, see [Calculate a simple summary](https://docs.coveo.com/en/n6je0405#calculate-a-simple-summary).

## Calculate the trend data

To calculate the trend data, run a query that retrieves the data from two date ranges in order to compare the current period to the previous period.
For example:

```sql
ALTER SESSION SET timezone = 'America/New_York';
-- We use variables as it's easier to change the date without having to replace them over all SQL queries.
SET START_DATE = '2023-05-19 00:00:00.000'::TIMESTAMP_TZ;
SET END_DATE = '2023-06-18 23:59:59.999'::TIMESTAMP_TZ;

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_searches_previous_period" AS (
    SELECT "datetime", "search_id", "visit_id", "custom_datas":"c_context_website"::string AS "website"
    FROM "COVEO"."ua"."searches_shared"
-- Computes the date range of the previous period to the current one, by subtracting the end date from the start date.
    WHERE convert_timezone('UTC', 'America/New_York', "datetime") BETWEEN DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $START_DATE) AND DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $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_clicks_previous_period" 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 DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $START_DATE) AND DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $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
), "cte_custom_events_previous_period" 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 DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $START_DATE) AND DATEADD(day, DATEDIFF(day, $END_DATE, $START_DATE) - 1, $END_DATE)
), "cte_card_summary" as (
    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')
), "cte_card_summary_previous_period" as (
    SELECT
        count(distinct "cte_searches_previous_period"."search_id" ) as "Search Event Count"
        , count(distinct "cte_clicks_previous_period"."click_id") as "Click Event Count"
        , count(distinct coalesce("cte_searches_previous_period"."visit_id", "cte_clicks_previous_period"."visit_id", "cte_custom_events_previous_period"."visit_id")) as "Unique Visits"
    FROM "cte_searches_previous_period" LEFT OUTER JOIN "cte_clicks_previous_period" ON "cte_searches_previous_period"."search_id" = "cte_clicks_previous_period"."search_id"
            FULL OUTER JOIN "cte_custom_events_previous_period" ON "cte_searches_previous_period"."search_id" = "cte_custom_events_previous_period"."search_id"
    WHERE coalesce("cte_searches_previous_period"."website","cte_clicks_previous_period"."website", "cte_custom_events_previous_period"."website") in ('engineering','Engineering')
)
SELECT  (select "Search Event Count" from "cte_card_summary_previous_period" ) as "Search From"
        , (select "Search Event Count" from "cte_card_summary" ) as "Search To"
        , (("Search To" - "Search From") / "Search From" * 100)::decimal(5,2) as "Search % change over last period"
        , (select "Click Event Count" from "cte_card_summary_previous_period" ) as "Click From"
        , (select "Click Event Count" from "cte_card_summary" ) as "Click To"
        , (("Click To" - "Click From") / "Click From" * 100)::decimal(5,2) as "Click % change over last period"
        , (select "Unique Visits" from "cte_card_summary_previous_period" ) as "Unique Visits From"
        , (select "Unique Visits" from "cte_card_summary" ) as "Unique Visits To"
        , (("Unique Visits To" - "Unique Visits From") / "Unique Visits From" * 100)::decimal(5,2) as "Unique Visits % change over last period";
```

Which results in:

![Trend data SQL results | Coveo](https://docs.coveo.com/en/assets/images/coveo-analytics/trend-data-sql-results.png)