SQL metrics measure whatever you model with valid SQL, but must include a SELECT statement that meets basic metric query requirements (details below). To create one, begin at Create a metric, and choose SQL at Step 1 (Metric Info).

Step 2 (Configure Metric)

After you finish Step 1 (Metric Info), Step 2 (Configure Metric) appears in the main form:

Under Place metric in Explorer make sure the right data asset is selected. This identifies where your metric will be placed in the Explorer tree.
Enter your SQL in the Custom SQL box. To produce a metric, your SQL clauses must have certain elements:
- Your SQL must SELECT at least two values, the timestamp and the aggregated metric value that you want the metric to compute. After validating the query, you will have the opportunity to select this timestamp as your timestamp, and select the metric value as the column. The timestamp will become the X-axis of your metric chart and the aggregated metric value will become the y-axis of your metric chart. If you are creating a sliced metric, you will additionally need the slice column in your SELECT.
- 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.
- The timestamp in your SELECT must match the aggregation interval that you choose in the drop down selection after you click Validate Query. For example, if your metric does a SELECT of a date, you cannot set Aggregation Interval to hourly.
- Your SQL must GROUP BY the timestamp column and if you specify a slice column, by the slice column as well.
- Note that if you set your SQL metric's Query Scope to Full Table, the query requirements change.
  - The SELECT clause doesn't need to select a timestamp.
  - The WHERE clause doesn't need start and end tags.
- See below for SQL examples for each supported datasource.
When your SQL is ready, select Validate Query. If the query validates, a number of fields in the form below will now become active.
- Set Column to the metric value in your SELECT statement
- Set Timestamp to the timestamp in your SELECT statement
- A number of additional selections are available which mirror the selections you need to make when configuring a table. Because the SQL metric does not inherit from a table, you must fill these out for the metric. Refer to Table configuration for details on how to fill in these fields.
- If your SQL selected an additional column and grouped by (in addition to the Timestamp column), select that column as your Slice Column.
- Optionally:
  - Add seasonality.
  - Set the Missing Value Filling: To exclude rows that have missing values when calculating metrics, choose Nan. To include these rows using 0 as the value in metric calculations, choose Zero.
  - Adjust the backfill duration.
  - Add a failing records query.
  - Add tags.

Partitions

If your table has a date based partition column, you can optimize your query to query only the partitions where the data resides. To do this, you need to do two things:

Add conditions to your WHERE clause which restrict the query to querying only the partitions where you expect your data to reside. This is done by adding the special tags {start_partition} and {end_partition} to your WHERE clause
Specify the format of that will be generated for {start_partition} and {end_partition} by filling in the Partition format input.This enables Lightup to convert {start_partition} and {end_partition} into string representations of the start and end of the query period using the date format specified in the Partitions input

For example, if Partition is set to "%Y-%m-%d-%H" and the query time range is from 3/1/2023 15:00:00 to 3/4/2023 17:00:00, {start_partition} will be set to 2023-03-01-15 and {end_partition} will be set to 2023-03-04-17. Let's assume you have a column in your dataset that is in this format, called datehour. You could now restrict your query to look only in the appropriate partitions by adding the following to your WHERE clause:

AND `datehour` >= {start_partition}
AND `datehour` <= {end_partition}

This will resolve to

AND `datehour` >= '2023-03-01-15'
AND `datehour` <= '2023-03-04-17'

It's important to note that this is a string comparison. This means that the comparison is alphabetical and in order for it to match, the value of your column (datehour in this case) must be lexicographically ordered within the start and end range specified by {start_partition} and {end_partition}. If your datehour column has a format that is different from the format that you specify as the partition format, the results will not make sense. For example, if datehour has format 2023-Mar-01-15, it will clearly not be found between 2023-03-01-15 and 2023-03-04-17.

It's also important to note that in order to use >= and <= as shown in the example above, the partition format must be lexicographically sortable. For example, if your column has the format 2023-1-15 (single digit for month), you cannot specify this format as your partition format because a lexicographical sort would result in 2023-12-25 being found between 2023-1-15 and 2023-2-15, which is clearly not correct.

SQL examples

SELECT
    date_trunc('hour', "timestamp") AS "event_ts",
    count(*) AS "cnt"
FROM
    "lightup_demo"."customer_orders_fact"
WHERE
    "timestamp" >= {start_ts}
    AND "timestamp" < {end_ts}
GROUP BY
    1

SELECT
    datetime_trunc(`Test_Time`, HOUR) AS `event_ts`,
    count(*) AS `cnt`
FROM
    `for_test_data_cache_v2.h06bbf7787854074cca66b9c8e3397593`
WHERE
    `Test_Time` >= {start_ts}
    AND `Test_Time` < {end_ts}
GROUP BY
    1

SELECT
    DATE_TRUNC('hour', `timestamp`) AS `event_ts`,
    count(*) AS `cnt`
FROM
    `lightup_demo`.`customer_orders_fact`
WHERE
    `datehour` >= {start_partition}
    AND `datehour` < {end_partition}
    AND `timestamp` >= {start_ts}
    AND `timestamp` < {end_ts}
GROUP BY
    1

SELECT
    date_trunc('hour', "timestamp") AS "event_ts",
    CAST(COUNT(*) AS INT) AS "cnt"
FROM
    "from_teradata"."customer_orders_fact"
WHERE
    "timestamp" >= {start_ts}
    AND "timestamp" < {end_ts}
GROUP BY
    1

SELECT
    "timestamp" AS "event_ts",
    count(*) AS "cnt"
FROM
    (
        SELECT
            dateadd(HOUR, datediff(HOUR, 0, "timestamp"), 0) AS "timestamp"
        FROM
            "testdata"."customer_orders_fact"
        WHERE
            "timestamp" >= {start_ts}
            AND "timestamp" < {end_ts}
    ) AS t
GROUP BY
    "timestamp"

SELECT
    "timestamp" AS "event_ts",
    COUNT(*) AS "cnt"
FROM
    (
        SELECT
            ROUND(
                (
                    CAST(
                        SYS_EXTRACT_UTC(
                            FROM_TZ(
                                CAST(
                                    TRUNC("timestamp" AT TIME ZONE 'UTC', 'HH24') AS TIMESTAMP
                                ),
                                'UTC'
                            )
                        ) AS DATE
                    ) - DATE '1970-01-01'
                ) * 86400
            ) AS "timestamp"
        FROM
            "lightup_demo"."customer_orders_fact"
        WHERE
            "timestamp" >= {start_ts}
            AND "timestamp" < {end_ts}
    )
GROUP BY
    "timestamp"

SELECT
    date_trunc('hour', "timestamp") AS "event_ts",
    count(*) AS "cnt"
FROM
    "testdata"."customer_orders_fact"
WHERE
    "timestamp" >= {start_ts}
    AND "timestamp" < {end_ts}
GROUP BY
    1

SELECT
    date_trunc('hour', "timestamp") AS "event_ts",
    count(*) AS "cnt"
FROM
    "testdata"."rummy"
WHERE
    "timestamp" >= {start_ts}
    AND "timestamp" < {end_ts}
GROUP BY
    1

SELECT
    date_trunc('HOUR', "LAST_UPDATED_DATE") AS "event_ts",
    count(*) AS "cnt"
FROM
    "PUBLIC"."CT_US_COVID_TESTS"
WHERE
    "LAST_UPDATED_DATE" >= {start_ts}
    AND "LAST_UPDATED_DATE" < {end_ts}
GROUP BY
    1

SELECT
    "timestamp" AS "event_ts",
    COUNT(*) AS "cnt"
FROM
    (
        SELECT
            (
                (
                    CAST(
                        (
                            CAST("timestamp" AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'GMT'
                        ) AS DATE AT TIME ZONE 'GMT'
                    ) - DATE '1970-01-01'
                ) * 86400 + (
                    EXTRACT(
                        HOUR
                        FROM
                            (
                                CAST("timestamp" AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'GMT'
                            )
                    ) * 3600
                ) + (
                    EXTRACT(
                        MINUTE
                        FROM
                            (
                                CAST("timestamp" AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'GMT'
                            )
                    ) * 60
                ) - (
                    EXTRACT(
                        MINUTE
                        FROM
                            (
                                CAST("timestamp" AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'GMT'
                            )
                    ) * 60
                )
            ) AS "timestamp"
        FROM
            "testdata"."customer_orders_fact"
        WHERE
            "timestamp" >= {start_ts}
            AND "timestamp" < {end_ts}
    ) AS t
GROUP BY
    "timestamp"

Step 3 (Preview)

Once you have configured your metric you can preview it to view its behavior over some time period. Review its behavior, modify the configuration if necessary, and then save it.