> For the complete documentation index, see [llms.txt](https://lightup.gitbook.io/user-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lightup.gitbook.io/user-guide/configure-data-quality-metrics/sql-metrics.md).

# SQL metrics

After you [finish **Step 1 (Metric Info)**](/user-guide/configure-data-quality-metrics.md#step-1-metric-info), **Step 2 (Configure Metric)** appears in the main form:

<figure><img src="/files/j6bzvVa3e6YJhp39ninm" alt="Step 2 (Configure Metric) of metric configuration for a &#x22;SQL&#x22; metric"><figcaption></figcaption></figure>

1. Unlike most metrics, a custom SQL metric isn't automatically associated with a data asset. To give your metric a home in the Explorer tree, under **Place metric in Explorer** select a data asset. &#x20;

![](/files/Mhry6rRhthfxSuvD2yI9)

2\. Enter your SQL in the **Custom SQL** box. To produce a metric, your SQL clauses must have certain elements:

* The **SELECT** clause must have at least two output columns: (1) a time period equal to the desired aggregation interval, extracted from a time-based column; and (2) an aggregated value column. These become (1) the y-axis and (2) the x-axis of your metric's chart. Any additional output columns must not be aggregated; these can be used for slices.&#x20;
* The **WHERE** clause must specify a date range using the time-based column and two tags: one producing the start of the range and another for the end of the range. These tags reflect the kind of time-based column you have: (1) use *{start\_ts}* and *{end\_ts}* for a timestamp column, (2) use *{start\_date}* and *{end\_date}* for a date column, and (3) use *{start\_datetime}* and *{end\_datetime}* for a datetime column. &#x20;
* Don't forget to **GROUP BY** the aggregation interval column.

See the tabs below for simple SQL examples for each supported datasource.&#x20;

{% tabs %}
{% tab title="Athena" %}
SELECT&#x20;

&#x20;    timestamp(date) as date,&#x20;

&#x20;    count(\*) AS count&#x20;

FROM lightup\_demo.dqfreshness

WHERE timestamp >= {start\_ts}&#x20;

&#x20;    AND timestamp < {end\_ts}&#x20;

GROUP BY date
{% endtab %}

{% tab title="BigQuery" %}
SELECT&#x20;

&#x20;    timestamp(date) as date,&#x20;

&#x20;    count(\*) AS count&#x20;

FROM lightup\_demo.dqfreshness

WHERE timestamp >= {start\_ts}&#x20;

&#x20;    AND timestamp < {end\_ts}&#x20;

GROUP BY date;
{% endtab %}

{% tab title="Databricks" %}
SELECT&#x20;

&#x20;    DATE\_TRUNC('day', "timestamp" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS "day",&#x20;

&#x20;    COUNT(\*) AS "value"&#x20;

FROM "lightup\_demo"."dqfreshness"&#x20;

WHERE "timestamp" >= {start\_ts}&#x20;

&#x20;    AND "timestamp" < {end\_ts}&#x20;

GROUP BY "day"
{% endtab %}

{% tab title="Microsoft SQL" %}
SELECT

&#x20;    DATETRUNC(hh, "timestamp") AS "hour",

&#x20;    COUNT(\*) as "value"

FROM

&#x20;    "lightup\_demo"."dqfreshness"

WHERE "timestamp" >= {start\_ts}

&#x20;    AND "timestamp" < {end\_ts}

GROUP BY

&#x20;    "hour"
{% endtab %}

{% tab title="Oracle" %}
SELECT&#x20;

&#x20;    TRUNC("timestamp", 'DD') AS "day",&#x20;

&#x20;    COUNT(\*) AS "count"&#x20;

FROM "lightup\_demo"."dqfreshness"&#x20;

WHERE "timestamp" >= {start\_ts}&#x20;

&#x20;    AND "timestamp" < {end\_ts}&#x20;

GROUP BY "day"
{% endtab %}

{% tab title="Postgres" %}
SELECT

&#x20;    date\_trunc('hour', "timestamp") AS "hour",

&#x20;    COUNT(\*) as "value"

FROM

&#x20;    "lightup\_demo"."dqfreshness"

WHERE

&#x20;    "timestamp" >= {start\_ts}

&#x20;    AND "timestamp" < {end\_ts}

GROUP BY

&#x20;    "hour"

{% endtab %}

{% tab title="Redshift" %}
SELECT

&#x20;    DATE\_TRUNC(hh, timestamp) AS "hour",

&#x20;    COUNT(\*) AS "count"

FROM lightup\_demo.dqfreshness

WHERE DATE\_TRUNC(hh, timestamp) >= {star&#x74;*\_*&#x74;s}

&#x20;    AND DATE\_TRUNC(hh, timestamp) < {end\_ts}

GROUP BY "hour"&#x20;
{% endtab %}

{% tab title="Snowflake" %}
SELECT

&#x20;    TRUNC("timestamp", hh) AS "hour",

&#x20;    COUNT(\*)

FROM "lightup\_demo"."dqfreshness"

WHERE TRUNC("timestamp", hh) >= {start\_ts}

&#x20;    AND TRUNC("timestamp", hh) < {end\_ts}

GROUP BY "hour" &#x20;
{% endtab %}
{% endtabs %}

{% hint style="info" %}
You can nest SELECT statements in your SQL. If you do, the clauses described above must be in the innermost SELECT statement.&#x20;
{% endhint %}

3\. When your SQL is ready, select **Validate Query**. If the query validates, you'll be able to select required setting values. Note that **Seasonality** and **Slices** are optional but recommended to improve query performance. &#x20;

4\. Add any tags, then in the top right corner select **Next** to proceed to **Step 3 (Preview)**.&#x20;

### Step 3 (Preview)

![](/files/caBoTqIzGuRcQkalx2XJ)

1. Select **View sample data** to see a sample set of rows from the data asset. Lightup returns 100 rows of data from the source table, including metadata. Select **Stop Viewing** when you're done.&#x20;
2. Optionally, adjust the date range for the preview. If you adjust the range, you must pick a start date and an end date.
3. Select **Preview** to see a chart of the metric for the date range from the preceding step.

After you're satisfied with the preview, select **Create** at the top right corner. The Explorer tab opens and displays your new metric in the Explorer tree.