SQL variables

SQL variables enable you to dynamically set values in your queries.

Creating SQL variables

To create a variable, go to the SQL editor, click the Variables tab, click Add variable, and choose your variable type (string, number, date, list, or boolean). Enter the variable name and value(s) and click Save and you'll be able to use it in any of your project's queries.

For example, you can create a list type variable with the key event_names and add events like $pageview and $autocapture as values.

Using variables in SQL queries

Once created, variables can be used in queries with the {variables.<variable-name>} syntax like this:

select * 
from events 
where event = {variables.event_names}

You can set the value for the variable in the same Variables tab, on the insight, or on the dashboard once you've created and added an SQL insight to it. For example, below we set the "event names" variable to $autocapture on the dashboard. This means every instance of {variables.event_names} in the queries on the dashboard is replaced with $autocapture.

Dashboard filter variables

Beyond the SQL variables you set up, there are also some variables that are automatically available to you when using SQL insights in a dashboard. You can access the dashboard's filters through the filters variable.

They include:

• filters.dateRange.from - Start date of the dashboard date range
• filters.dateRange.to - End date of the dashboard date range
• filters.properties - Array of property filters applied at the dashboard level
• filters.breakdown_filter - Breakdown configuration from dashboard filters
• filters.compare - Comparison settings if enabled
• filters.interval - Time interval setting from dashboard

For example, to add the dashboard's date range to the query, you can use the filters.dateRange.from and filters.dateRange.to variables like this:

select * 
from events 
where event = {variables.event_names}
and timestamp >= {filters.dateRange.from} and timestamp < {filters.dateRange.to}