AQL Cheatsheet - Functions

Aggregate Function

count()

Syntax

count([table], expression)

Description

Counts the total number of items in a group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Count total orders
orders
| count(orders.id)

Count completed orders
orders
| count(orders.id)
| where(orders.status == 'completed')

Count orders per country
orders
| group(orders.delivery_country)
| select(orders.delivery_country, total_orders: count(orders.id))

Inputs

expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The count of non-NULL values.

Learn more

count (AQL Doc)

count_if()

Syntax

count_if([table], condition)

Description

Counts the total rows from one table that satisfy the given condition.

Tags

Function, Aggregate Function, Condition

Examples

Count orders where delivery country is USA
orders
| count_if(orders.delivery_country == 'USA')

Filtered Count shipped orders
orders
| count_if(orders.status == 'shipped')

Count shipped orders per country
orders
| group(orders.delivery_country)
| select(orders.delivery_country, shipped_orders: count_if(orders.status == 'shipped'))

Inputs

condition (Truefalse: required): A field or an AQL expression that evaluates to true or false.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The count of rows that satisfy the condition.

Learn more

count_if (AQL Doc)

count_distinct()

Syntax

count_distinct([table], expression)

Description

Counts the total number of distinct items in a group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Count unique customers
orders
| count_distinct(orders.customer_id)

Count unique customers in USA
customers
| count_distinct(customers.id)
| where(customers.country == 'USA')

Count unique delivery countries per product
products
| group(products.id)
| select(products.id, total_countries: count_distinct(orders.delivery_country))

Inputs

expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The count of distinct non-NULL values.

Learn more

count_distinct (AQL Doc)

average()

Syntax

average([table], expression)

Description

Calculates the average of values in a group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Average order value
orders
| average(orders.value)

Average order value for completed orders
orders
| average(orders.value)
| where(orders.status == 'completed')

Average order value per country
orders
| group(orders.delivery_country)
| select(orders.delivery_country, avg_order_value: average(orders.value))

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The average of the values.

Aliases

avg()

Learn more

average (AQL Doc)

min()

Syntax

min([table], expression)

Description

Returns the item in the group with the smallest value, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Basic Usage: Minimum order value
orders
| min(orders.value)

Get the minimum completed order value
orders
| min(orders.value)
| where(orders.status == 'completed')

Get the minimum order value per country
orders
| group(orders.delivery_country)
| select(orders.delivery_country, min_order_value: min(orders.value))

Inputs

expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Any: The minimum value.

Learn more

min (AQL Doc)

max()

Syntax

max([table], expression)

Description

Returns the item in the group with the largest value, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Get the maximum order value
orders
| max(orders.value)

Get the maximum completed order value
orders
| max(orders.value)
| where(orders.status == 'completed')

Get the maximum order value per country
orders
| group(orders.delivery_country)
| select(orders.delivery_country, max_order_value: max(orders.value))

Inputs

expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Any: The maximum value.

Learn more

max (AQL Doc)

sum()

Syntax

sum([table], expression)

Description

Calculates the sum of values in the group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Calculate the sum of order value
orders
| sum(orders.value)

Calculate the sum of completed order value
orders
| sum(orders.value)
| where(orders.status == 'completed')

Calculate the sum of order value per country
orders
| group(orders.delivery_country)
| select(orders.delivery_country, total_order_value: sum(orders.value))

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The sum of the values.

Learn more

sum (AQL Doc)

median()

Syntax

median([table], expression)

Description

Computes the median of the values in the group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Get the median order quantity
median(orders.quantity)

Get the median order quantity (using pipe)
orders
| median(orders.quantity)

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The median of the values.

Learn more

median (AQL Doc)

stdev()

Syntax

stdev([table], expression)

Description

Computes the sample standard deviation of the values in the group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Calculate the sample standard deviation of order value
stdev(orders.value)

Calculate the sample standard deviation of order value (using pipe)
orders
| stdev(orders.value)

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The sample standard deviation.

Learn more

stdev (AQL Doc)

stdevp()

Syntax

stdevp([table], expression)

Description

Computes the population standard deviation of the values in the group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Calculate the population standard deviation of order value
stdevp(orders.value)

Calculate the population standard deviation of order value (using pipe)
orders
| stdevp(orders.value)

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The population standard deviation.

Learn more

stdevp (AQL Doc)

var()

Syntax

var([table], expression)

Description

Returns the sample variance of the values in the group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Calculate the sample variance of order value
var(orders.value)

Calculate the sample variance of order value (using pipe)
orders
| var(orders.value)

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The sample variance.

Learn more

var (AQL Doc)

varp()

Syntax

varp([table], expression)

Description

Returns the population variance of the values in the group, excluding NULL values.

Tags

Function, Aggregate Function

Examples

Calculate the population variance of order value
varp(orders.value)

Calculate the population variance of order value (using pipe)
orders
| varp(orders.value)

Inputs

expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.

Output

Number: The population variance.

Learn more

varp (AQL Doc)

string_agg()

Syntax

string_agg([table], expression, [sep: _sep], [distinct: _distinct], [order: _order])

Description

Returns a text that is the concatenation of all values of the expression.

Tags

Function, Aggregate Function

Examples

Basic usage: Concatenate all product names
string_agg(products.name)

Concatenate product names with a separator
string_agg(products.name, sep: ', ')

Concatenate distinct product names with a separator
string_agg(products.name, sep: ', ', distinct: true)

Concatenate product names, ordered by name
string_agg(products.name, order: 'asc')

Concatenate product names, ordered by name (descending)
string_agg(products.name, order: 'desc')

Inputs

expression (Text: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
_sep (Text: optional): Separator between values, default is ','.
_distinct (Truefalse: optional): If true, only distinct values are concatenated, default is false.
_order (Text: optional): Specifies the ordering of values ('asc' or 'desc'), default is not specified.

Output

Text: The concatenated text of values.

Learn more

string_agg (AQL Doc)

corr()

Syntax

corr(table, field1, field2)

Description

Returns the Pearson correlation coefficient of two number fields in the table.

Tags

Function, Aggregate Function

Examples

Calculate the correlation between age and order value
corr(users, users.age, orders.value)

Inputs

table (Table: required): The table containing the fields to correlate.
field1 (Number: required): The first number field to correlate.
field2 (Number: required): The second number field to correlate.

Output

Number: The Pearson correlation coefficient between the two fields.

Learn more

corr (AQL Doc)

max_by()

Syntax

max_by(table, value, by)

Description

Returns the value of value from the row where by is maximum.

Tags

Function, Aggregate Function

Examples

Get the name of the customer with the highest order value
max_by(orders, orders.customer_name, orders.value)

Inputs

table (Table: required): The table to find the maximum value in.
value (Any: required): The field to return from the row with the maximum value.
by (Any: required): The expression to use for determining the maximum value.

Output

Any: The value of value from the row where by is maximum.

Learn more

max_by (AQL Doc)

min_by()

Syntax

min_by(table, value, by)

Description

Returns the value of value from the row where by is minimum.

Tags

Function, Aggregate Function

Examples

Get the name of the customer with the lowest order value
min_by(orders, orders.customer_name, orders.value)

Inputs

table (Table: required): The table to find the minimum value in.
value (Any: required): The field to return from the row with the minimum value.
by (Any: required): The expression to use for determining the minimum value.

Output

Any: The value of value from the row where by is minimum.

Learn more

min_by (AQL Doc)

percentile_cont()

Syntax

percentile_cont([table], expression, percentile)

Description

Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed.

Tags

Function, Aggregate Function

Examples

Basic usage: Calculate 70th percentile of user ages
percentile_cont(ecommerce_users.age, 0.7)

Full form usage: Calculate 70th percentile of user ages
ecommerce_users | percentile_cont(ecommerce_users.age, 0.7)

Nested aggregation: Calculate 70th percentile of total value by users
unique(ecommerce_users.id) | percentile_cont(total_value, 0.7)

Inputs

expression (Any: required): A field or an AQL expression to be evaluated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
percentile (Number: required): The percentile to compute. Must be a value between 0 and 1.

Output

Any: The percentile value.

Learn more

percentile_cont (AQL Doc)

percentile_disc()

Syntax

percentile_disc([table], expression, percentile)

Description

Returns the value at the given percentile of the sorted expression values. If the percentile falls between two values, a discrete value will be returned (the logic to select the value is database dependent).

Tags

Function, Aggregate Function

Examples

Basic usage: Calculate 70th percentile of user ages
percentile_disc(ecommerce_users.age, 0.7)

Full form usage: Calculate 70th percentile of user ages
ecommerce_users | percentile_disc(ecommerce_users.age, 0.7)

Nested aggregation: Calculate 70th percentile of total orders by users
unique(ecommerce_users.id) | percentile_disc(count(ecommerce_orders.id), 0.7)

Inputs

expression (Any: required): A field or an AQL expression to be evaluated.
table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
percentile (Number: required): The percentile to compute. Must be a value between 0 and 1.

Output

Any: The percentile value.

Learn more

percentile_disc (AQL Doc)

Condition Function

where()

Syntax

where(metric, condition1, [condition2, ...])

metric | where(condition1, [condition2, ...])

Description

Calculates a metric with specified conditions applied. The where function allow applying conditions to a metric without needing to manually modifying its original definition and filters data at the source before any calculations occur. It works similar to Dashboard Filters.

Difference vs filter: where pushes conditions down to the target model before calculations begin. In contrast, filter evaluates conditions locally after retrieving the data. Due to this key difference, condition in where can only target pre-defined dimension in modeling, and only support some form of conditions. For more information, please check the full documentation.

Tags

Function, Metric Function, Condition Function, Condition

Examples

Calculate total order value for completed orders
total_order_value
| where(orders.status == 'completed')

Total value of top 5 buyers by volumn
total_order_value
| where(users.id in top(5, users.id, by: count(orders.id)))

Revenue generated in the last 30 days
sum(orders.value)
| where(orders.created_at matches @(last 30 days))

Inputs

metric (Any: required): The original metric to which the condition will be applied.
condition1 (Condition: required): The condition to restrict the metric calculation.
[condition2, ...] (Condition: optional, repeatable): Additional conditions to apply.

Output

Any: The original metric calculated with the specified conditions applied.

Learn more

where (AQL Doc)

Level Of Detail Function

of_all()

Syntax

of_all(metric, [model, dimension, ...], [keep_filters: false])

metric | of_all([model, dimension, ...], [keep_filters: false])

Description

Returns a metric evaluated without certain dimensions or grains. The of_all function removes specified dimensions from the calculation context. It is commonly used for calculating percent of total and enables comparisons between metrics at different Levels of Detail (LoD). The function has aliases exclude_grains and exclude that provides identical functionality.

Tags

Function, Metric Function, Level of Detail Function

Examples

Calculate percent of total across all countries (i.e ignoring country dimension)
order_value / (
  order_value
  | of_all(order_items.country)
)

Count the orders excluding category dimension
count(orders.id)
| of_all(orders.category)

Calculate the delta of order value vs the average across all country
order_value - (avg(unique(order_items.country)
| select(order_value))
| of_all(order_items.country))

Inputs

metric (Any: required): The metric to exclude dimensions from.
model (Model: optional, repeatable): Model whose dimensions should be excluded.
dimension (Dimension: optional, repeatable): Specific dimensions to exclude.
keep_filters (Truefalse: optional): Whether to keep filters applied on excluded dimensions. Default is false.

Output

Any: The original metric calculation without the specified dimensions.

Aliases

exclude_grains(), exclude()

Learn more

of_all (AQL Doc)

keep_grains()

Syntax

keep_grains(metric, [model, dimension, ...], [keep_filters: false])

metric | keep_grains([model, dimension, ...], [keep_filters: false])

Description

Calculates a metric only against the specified dimensions or grains, ignoring all other dimensions. The keep_grains function ensures that calculations maintain a consistent level of detail, prevents additional query fields from affecting the metric, and works only with dimensions already present in the context. Note that it will not add the grains to the Level of Detail context if they aren't present before calling keep_grains.

Tags

Function, Metric Function, Level of Detail Function

Aliases

keep()

Examples

Calculate the total spent, keeping users grains
// This keeps the grain of the entire user model during the metric calculation
sum(order_items.order_value)
| keep_grains(users)

Average order value calculation, keeping users.id grain
average_order_value
| keep_grains(users.id)

Inputs

metric (Any: required): The input metric to modify.
model (Model: optional, repeatable): Model whose dimensions should be kept.
dimension (Dimension: optional, repeatable): Specific dimensions to keep.
keep_filters (Truefalse: optional): Whether to keep filters applied on excluded dimensions. Default is false.

Output

Any: The original metric calculation using only the specified dimensions.

Learn more

keep_grains (AQL Doc)

dimensionalize()

Syntax

dimensionalize(metric, [dimension, ...])

metric | dimensionalize([dimension, ...])

Description

Calculates a metric at a specific Level of Detail (LoD), regardless of the outer query context. The dimensionalize function evaluates metrics at a fixed grain and turns the result into a dimension value. It is commonly used for cohort analysis and customer lifetime value calculations, and only works inside dimension definitions.

Difference vs keep_grains: Unlike keep_grains which retains specified grains in metric calculation, dimensionalize evaluates the calculation at specified grains and converts it into a dimension value. It only works inside dimension definitions.

Tags

Function, Metric Function, Level of Detail Function

Examples

Use the customer lifetime value as a dimension
sum(orders.amount)
| dimensionalize(users.id)

Use the customer cohort as a dimension
min(users.created_at | month())
| dimensionalize(users.id)

Inputs

metric (Any: required): The metric to evaluate at a fixed grain.
dimension (Dimension: optional, repeatable): The dimension(s) to use as the Level of Detail.

Output

Any: The metric value at the specified granularity as a dimension value.

Learn more

dimensionalize (AQL Doc)

Logical Function

case()

Syntax

case(when: condition, then: value, [when: condition_expr, then: value, ...], [else: value])

Description

Returns the value associated with the first condition that evaluates to true. Note that the return types of all then and else expressions must be consistent.

Tags

Function, Logical Function, Condition

Examples

Categorize based on user gender
case(
  when: users.gender == 'm', then: 'male',
  when: users.gender == 'f', then: 'female',
  else: 'others'
)

Inputs

when (Truefalse: required): The condition to evaluate.
then (Any: required): The value to return if the condition is met.
else (Any: optional): The value to return if no conditions are met.

Output

Any: The value returned based on the first met condition, or the else value if provided and no conditions are met.

Learn more

case (AQL Doc)

and()

Syntax

and(condition, ...)

Description

Returns true only when all specified conditions are true.

Tags

Function, Logical Function, Condition

Examples

Filter products with IDs >= 2 and <= 8
and(products.id >= 2, products.id <= 8)

Inputs

condition (Truefalse: required, repeatable): The conditions to AND together.

Output

Truefalse: Returns true if all input conditions are true, else false.

Learn more

and (AQL Doc)

or()

Syntax

or(condition, ...)

Description

Returns true when at least one of the specified conditions is true.

Tags

Function, Logical Function, Condition

Inputs

condition (Truefalse: required, repeatable): The conditions to OR together.

Output

Truefalse: Returns true if any input condition is true, else false.

Examples

Filter products with IDs <= 2 or >= 8
or(products.id <= 2, products.id >= 8)

Learn more

or (AQL Doc)

not()

Syntax

not(condition)

Description

Logical NOT takes a single truefalse expression and returns true when the expression is false.

Tags

Function, Logical Function, Condition

Examples

Filter non-null product IDs
not(is(products.id, null))

Inputs

condition_expr (Truefalse: required): The condition to negate.

Output

Truefalse: Returns true if the input condition is false, else false.

Learn more

not (AQL Doc)

Time-based Function

running_total()

Syntax

running_total(metric, [running_dimension, ...], [keep_filters: false])

metric | running_total([running_dimension, ...], [keep_filters: false])

Description

Calculates a running total of a metric along specified dimensions from the starting point to the current period. By default, the running total is calculated after filtering (keep_filters: true). To calculate a running total of all data, ignoring any filters on the running dimensions, you can set keep_filters: false.

Tags

Function, Metric Function, Time-based Function, Cumulative Calculation, Moving Calculation

Examples

Calculate running total of orders along created_at dimension
running_total(orders.total_orders, orders.created_at)

Calculate running total of orders along multiple dimensions
running_total(orders.total_orders, orders.created_at, orders.status)

Calculate running total of orders ignoring filters on running dimensions
running_total(orders.total_orders, orders.created_at, keep_filters: false)

Inputs

metric (Any: required): The metric to calculate the running total for.
running_dimension (Dimension: optional, repeatable): The dimension(s) to run the calculation along. If not specified, the calculation will run along all date dimensions in the exploration.
keep_filters (Truefalse: optional): Whether to keep the filters applied on the running dimensions. Default is true.

Output

Any: A new metric that runs along the specified dimension(s).

Learn more

running_total (AQL Doc)

period_to_date()

Syntax

period_to_date(metric, date_part, date_dimension)

metric | period_to_date(date_part, date_dimension)

Description

Calculates a metric from the beginning of a specified time period (year, quarter, month, etc.) to the current date. This function is commonly used to calculate Year-to-Date (YTD), Quarter-to-Date (QTD), or Month-to-Date (MTD) metrics.

Note: "Current date" refers to the last date in the context of the current row, not the current month/period on your calendar.

Tags

Function, Metric Function, Time-based Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the year-to-date total orders
count(orders.id)
| period_to_date('year', orders.created_at)

Calculate the month-to-date total orders
count(orders.id)
| period_to_date('month', orders.created_at)

Inputs

metric (Any: required): The metric to apply the period_to_date function to.
date_part (Text: required): The time period for which the metric should reset. Can be one of: 'year', 'quarter', 'month', 'week', 'day'.
date_dimension (Dimension: required): The date dimension used to determine the reset period.

Output

Any: The input metric calculation from the beginning of the specified time period to the current date.

Learn more

period_to_date (AQL Doc)

exact_period()

Syntax

exact_period(metric, time_dimension, time_range)

metric | exact_period(time_dimension, time_range)

Description

Calculates a metric within a custom time period. This function can be used to compare how a metric performs in a specific period compared to another period. It overrides any applied time filters.

Tags

Function, Metric Function, Time-based Function

Examples

Calculate the total orders in a specific time range
exact_period(orders.total_orders, orders.created_at, @2022-07-01 - 2022-09-01)

Calculate the total orders in a specific time range (pipe syntax)
orders.total_orders
| exact_period(orders.created_at, @2022-07-01 - 2022-09-01)

Inputs

metric (Any: required): The metric to calculate within the custom period.
time_dimension (Dimension: required): A pre-defined datetime/date dimension used for matching periods in the time range to the period in the dimension.
time_range (Datetime: required): A datetime literal that specifies the exact time range, e.g., @2022-04-01, @2022, @(last 2 weeks).

Output

Any: The input metric calculation for the specified custom period.

Learn more

exact_period (AQL Doc)

relative_period()

Syntax

relative_period(metric, time_dimension, time_interval)

metric | relative_period(time_dimension, time_interval)

Description

Calculates a metric in the active time range shifted by a specified interval. The active time range can be the range specified in a filter (if no time dimension is active) or the time period in each row of a time dimension.

Tags

Function, Metric Function, Time-based Function

Examples

Calculate the total orders shifted by 1 month
relative_period(orders.total_orders, orders.created_at, interval(-1 month))

Calculate the total orders shifted by 1 month (pipe syntax)
orders.total_orders
| relative_period(orders.created_at, interval(-1 month))

Inputs

metric (Any: required): The metric to calculate within the shifted time range.
time_dimension (Dimension: required): A pre-defined datetime/date dimension used for shifting.
time_interval (Interval: required): A relative interval for shifting from the active time condition, e.g., interval(-1 month).

Output

Any: The input metric calculated in the active time range shifted by the specified interval.

Learn more

relative_period (AQL Doc)

trailing_period()

Syntax

trailing_period(metric, date_dimension, period)

metric | trailing_period(date_dimension, period)

Description

Calculates a metric over a specific number of date periods up to the current period. This function is commonly used to calculate metrics like Trailing 3 Months, which determines the total orders in the last 3 months up until the current month.

Note: "Current period" refers to the month/period in the context of the current row, not the current month/period on your calendar.

Tags

Function, Metric Function, Time-based Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the total orders in the last 3 months
trailing_period(count(orders.id), orders.created_at, interval(3 months))

Calculate the total orders in the last 3 months (pipe syntax)
count(orders.id)
| trailing_period(orders.created_at, interval(3 months))

Inputs

metric (Any: required): The metric to apply the trailing_period function to.
date_dimension (Dimension: required): The date dimension used to determine the periods.
period (Interval: required): An interval literal that specifies the number of periods to calculate (includes the current period), e.g., interval(3 months), interval(1 year).

Output

Any: The input metric calculated over the specified number of date periods up to the current period.

Learn more

trailing_period (AQL Doc)

Time Intelligence Function

epoch()

Syntax

epoch([datetime])

Description

Returns a Unix timestamp which is the number of seconds that have elapsed since '1970-01-01 00:00:00' UTC.

Tags

Function, Time Intelligence Function

Examples

Get timestamp for a specific datetime
epoch(orders.created_at)

Get timestamp for a date
epoch(@2022-01-03)

Inputs

datetime (Datetime: optional): The date or datetime to convert to a Unix timestamp. If not provided, uses the current date time.

Output

Number: Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC.

day()

Syntax

day(datetime_dimension)

datetime_dimension | day()

Description

Truncates a datetime_dimension value to the first day of the day (midnight).

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime dimension
day(orders.created_at)

Using pipe syntax
orders.created_at | day()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the day

Output

Datetime Dimension: Truncated to the first moment of the day

week()

Syntax

week(datetime_dimension)

datetime_dimension | week()

Description

Truncates a datetime_dimension value to the first day of the week. Weeks begin on the day set in the Week Start Day Setting.

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime
week(orders.created_at)

Using pipe syntax
orders.created_at | week()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the week

Output

Datetime Dimension: Truncated to the first moment of the week

month()

Syntax

month(datetime_dimension)

datetime_dimension | month()

Description

Truncates a datetime_dimension value to the first day of the month.

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime
month(orders.created_at)

Using pipe syntax
orders.created_at | month()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the month

Output

Datetime Dimension: Truncated to the first moment of the month

quarter()

Syntax

quarter(datetime_dimension)

datetime_dimension | quarter()

Description

Truncates a datetime_dimension value to the first day of the quarter.

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime
quarter(orders.created_at)

Using pipe syntax
orders.created_at | quarter()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the quarter

Output

Datetime Dimension: Truncated to the first moment of the quarter

year()

Syntax

year(datetime_dimension)

datetime_dimension | year()

Description

Truncates a datetime_dimension value to the first day of the year.

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime
year(orders.created_at)

Using pipe syntax
orders.created_at | year()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the year

Output

Datetime Dimension: Truncated to the first moment of the year

hour()

Syntax

hour(datetime_dimension)

datetime_dimension | hour()

Description

Truncates a datetime_dimension value to the first minute of the hour.

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime dimension
hour(orders.created_at)

Using pipe syntax
orders.created_at | hour()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the hour

Output

Datetime Dimension: Truncated to the first moment of the hour

minute()

Syntax

minute(datetime_dimension)

datetime_dimension | minute()

Description

Truncates a datetime_dimension value to the first second of the minute.

Tags

Function, Time Intelligence Function

Examples

Truncate a specific datetime dimension
minute(orders.created_at)

Using pipe syntax
orders.created_at | minute()

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate to the minute

Output

Datetime Dimension: Truncated to the first moment of the minute

date_trunc()

Syntax

date_trunc(datetime_dimension, datetime_part)

Description

Truncates a datetime_dimension value to the granularity of datetime_part. The datetime value is rounded to the beginning of datetime_part.

Supported parts: 'day', 'week', 'month', 'quarter', 'year', 'hour', 'minute'

Tags

Function, Time Intelligence Function

Examples

Truncate to day
date_trunc(orders.created_at, 'day')

Truncate to month
date_trunc(orders.created_at, 'month')

Truncate to year
date_trunc(orders.created_at, 'year')

Inputs

datetime_dimension (Datetime Dimension: required): The datetime to truncate
datetime_part (String: required): The granularity to truncate to

Output

Datetime Dimension: Truncated to the beginning of the specified part

date_part()

Syntax

date_part(datetime_part, datetime)

Description

Extracts a specific numeric part from a date or datetime value. Returns the numeric representation of the specified part of the date.

Tags

Function, Time Intelligence Function

Examples

Extract year
date_part('year', orders.created_at)

Extract quarter
date_part('quarter', orders.created_at)

Extract month
date_part('month', orders.created_at)

Inputs

datetime_part (String: required): The specific part of the datetime to extract
datetime (Datetime: required): The datetime to extract the part from

Output

Number: Integer representing the specified part of the date

year_num()

Syntax

year_num(datetime)

datetime | year_num()

Description

Extracts the numeric year from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract year number
year_num(orders.created_at)

Using pipe syntax
orders.created_at | year_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the year from

Output

Number: Numeric representation of the year

quarter_num()

Syntax

quarter_num(datetime)

datetime | quarter_num()

Description

Extracts the quarter number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract quarter number
quarter_num(orders.created_at)

Using pipe syntax
orders.created_at | quarter_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the quarter from

Output

Number: Quarter number (1-4)

month_num()

Syntax

month_num(datetime)

datetime | month_num()

Description

Extracts the month number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract month number
month_num(orders.created_at)

Using pipe syntax
orders.created_at | month_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the month from

Output

Number: Month number (1-12)

week_num()

Syntax

week_num(datetime)

datetime | week_num()

Description

Extracts the week number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract week number
week_num(orders.created_at)

Using pipe syntax
orders.created_at | week_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the week number from

Output

Number: Week number of the year (1-53)

dow_num()

Syntax

dow_num(datetime)

datetime | dow_num()

Description

Alias for dayofweek_num, extracts the day of week number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract day of week number
dow_num(orders.created_at)

Using pipe syntax
orders.created_at | dow_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the day of week from

Output

Number: Day of week number (0-6)

Aliases

dayofweek_num()

day_num()

Syntax

day_num(datetime)

datetime | day_num()

Description

Extracts the day of month number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract day number
day_num(orders.created_at)

Using pipe syntax
orders.created_at | day_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the day from

Output

Number: Day of month number (1-31)

hour_num()

Syntax

hour_num(datetime)

datetime | hour_num()

Description

Extracts the hour number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract hour number
hour_num(orders.created_at)

Using pipe syntax
orders.created_at | hour_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the hour from

Output

Number: Hour number (0-23)

minute_num()

Syntax

minute_num(datetime)

datetime | minute_num()

Description

Extracts the minute number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract minute number
minute_num(orders.created_at)

Using pipe syntax
orders.created_at | minute_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the minute from

Output

Number: Minute number (0-59)

second_num()

Syntax

second_num(datetime)

datetime | second_num()

Description

Extracts the second number from a datetime value.

Tags

Function, Time Intelligence Function

Examples

Extract second number
second_num(orders.created_at)

Using pipe syntax
orders.created_at | second_num()

Inputs

datetime (Datetime Dimension: required): The datetime to extract the second from

Output

Number: Second number (0-59)

date_diff()

Syntax

date_diff(datetime_part, start, end)

Description

Calculates the difference between two dates in the specified datetime_part.

Supported parts: 'day', 'week', 'month', 'quarter', 'year'

Tags

Function, Time Intelligence Function

Examples

Calculate days between created_at and now
date_diff('day', orders.created_at, @now)

Calculate months between created_at and now
date_diff('month', orders.created_at, @now)

Inputs

datetime_part (String: required): The unit of measurement for the difference
start (Datetime: required): The starting date
end (Datetime: required): The ending date

Output

Number: The difference between the two dates in the specified part

date_format()

Syntax

date_format(datetime, format)

Description

Formats a date according to the specified format string.

Tags

Function, Time Intelligence Function

Examples

Format a date
date_format(orders.created_at, '%Y-%m-%d')

Format a date with time
date_format(orders.created_at, '%Y-%m-%d %H:%M:%S')

Format a date with month name
date_format(orders.created_at, '%B %d, %Y')

Format a date with day of the week
date_format(orders.created_at, '%A, %B %d, %Y')

Inputs

datetime (Datetime: required): The date to format
format (Text: required): The format string

Output

Text: The formatted date string

Format

Pattern	Description	Example
`%Y`	Four-digit year	`2018`
`%y`	Two-digit year	`18`
`%q`	Quarter of the year (1–4)	`3`
`%m`	Two-digit month	`07`
`%B`	Full month name	`July`
`%b`	Abbreviated month name	`Jul`
`%A`	Full day of week	`Sunday`
`%a`	Abbreviated day of week	`Sun`
`%d`	Two-digit day of month (01-31)	`08`
`%H`	Two-digit hour based on 24-hour clock (00–23)	`00`
`%I`	Two-digit hour based on 12-hour clock (01–12)	`12`
`%M`	Two-digit minutes (00–59)	`34`
`%S`	Two-digit seconds (00–59)	`59`
`%p`	AM or PM	`AM`
`%L`	Three-digit milliseconds (000–999)	`000`
`%f`	Six-digit microseconds (000000–999999)	`000000`
`%%`	The percent sign	`%`

Note: Not all format patterns are supported by all databases.

Learn more

date_format (AQL Doc)

from_unixtime()

Syntax

from_unixtime(number)

Description

Converts a Unix timestamp (seconds since epoch) to a datetime value.

Tags

Function, Time Intelligence Function

Examples

Convert a Unix timestamp
from_unixtime(1672531200)

Inputs

number (Number: required): The Unix timestamp to convert

Output

Datetime: The corresponding datetime value

Learn more

from_unixtime (AQL Doc)

last_day()

Syntax

last_day(datetime, date_part)

Description

Returns the last day of the period for a given date.

Tags

Function, Time Intelligence Function

Examples

Get the last day of the month
last_day(orders.created_at, 'month')

Get the last day of the quarter
last_day(orders.created_at, 'quarter')

Inputs

datetime (Datetime: required): The date to extract the last day of the period from
date_part (Text: required): The string to specify the period of the date to return the last day of. Can be one of 'month', 'year', 'quarter', 'week'.

Output

Date: The last day of the period for the given date

Learn more

last_day (AQL Doc)

age()

Syntax

age(datetime)

datetime | age()

Description

Returns the age in years.

Tags

Function, Time Intelligence Function

Examples

Calculate age from registration date
age(users.registration_date)

Calculate age from birthdate using pipe syntax
users.birthdate | age()

Inputs

datetime (Datetime: required): The datetime to calculate the age from

Output

Number: The age in years

Learn more

age (AQL Doc)

Table Function

select()

Syntax

select(table, field1, [field2, ...])

table | select(field1, [field2, ...])

Description

Returns a table containing only the specified fields. The select function serves two main purposes: extracting specific columns from a table and creating calculated fields for use with other table functions (such as sum, filter, etc.). When defining a new calculated field, you must assign a name using the syntax field_name: expression.

Tags

Function, Table Function

Examples

Select user ID and email from the users table
users
| select(users.id, users.email)

Define a new column full_name for the users table
users
| select(full_name: concat(users.first_name, " ", users.last_name))

Calculate total orders value
order_items
| select(value: order_items.quantity * products.price)
| sum(value)

Inputs

table (Table: required): The source table to select fields from.
field1 (Any: required): The first field to select from the table.
[field2, ...] (Any: optional, repeatable): Additional fields to select from the table.

Output

Table: A new table containing only the specified fields.

Learn more

select (AQL Doc)

group()

Syntax

group(table, dimension1, [dimension2, ...])

table | group(dimension1, [dimension2, ...])

Description

Returns a table grouped by one or more specified dimensions. The group function aggregates data by the specified dimensions and creates a grouped table as input for Nested Aggregation. It must be followed by either select or filter.

Note: For most Nested Aggregation cases we actually recommend using unique(dimension) | select(aggregation) as it's shorter and easier to use.

Tags

Function, Table Function

Examples

Count the number of users per country
users
| group(users.country)
| select(users.country, user_count: count(users.id))

Find all countries that have more than 1000 users
users
| group(users.country)
| filter(count(users.id) > 1000)

Average of (total order value by user ID)
users
| group(users.id)
| select(users.id, total_order_value)
| average(total_order_value)

Inputs

table (Table: required): The input table to group.
dimension1 (Dimension: required): The first dimension to group by.
[dimension2, ...] (Dimension: optional, repeatable): Additional dimensions to group by.

Output

Table: A table grouped by the specified dimensions.

Learn more

group (AQL Doc)

filter()

Syntax

filter(table, condition1, [condition2, ...])

table | filter(condition1, [condition2, ...])

Description

Returns a table containing only the rows that satisfy one or more specified conditions. The filter function narrows down a table based on specific criteria by applying conditions locally to each row of the input table. It can be used before further transformations or aggregations.

Difference vs where: filter evaluates conditions for each row locally after data is retrieved. In contrast, where pushes conditions down to the target model before any calculation happens, similar to a Dashboard Filter.

Tags

Function, Table Function, Condition

Examples

Filter users from Canada
users
| filter(users.country == 'Canada')

Filter users who buy within 3 days of activation
users
| filter(date_diff('day', users.created_at, users.first_buy_at) <= 3)

Filter users with premium orders
users
| group(users.id)
| filter(sum(orders.value) > 1000)

Inputs

table (Table: required): The input table expression to filter.
condition1 (Truefalse: required): The first condition to apply in the filter.
[condition2, ...] (Truefalse: optional, repeatable): Additional filter conditions.

Output

Table: A table containing only rows that meet the specified conditions.

Learn more

filter (AQL Doc)

unique()

Syntax

unique(dimension1, [dimension2, ...])

Description

Returns a table with all unique combinations of the specified dimensions. The unique function identifies distinct values in one or more fields and creates a table suitable for grouping in Nested Aggregation. It is recommended over group for most Nested Aggregation use cases.

Tags

Function, Table Function

Examples

Get unique countries from the users table
unique(users.country)

Average of (total order value by user ID)
unique(users.id)
| select(total_order_value)
| average()

Inputs

dimension1 (Dimension: required): The dimension to get unique values from.
[dimension2, ...] (Dimension: optional, repeatable): Additional dimensions to get unique values from.

Output

Table: A table containing unique combinations of the specified dimensions.

Learn more

unique (AQL Doc)

top()

Syntax

top(n, dimension, by: metric, [logic])

Description

Returns the top N values of a specified dimension based on a metric. The top function sorts rows in descending order by the specified metric. By default, it uses 'skip' ranking logic (similar to the rank function), but can use 'dense' ranking logic when specified with logic: 'dense'.

Tags

Function, Table Function

Examples

Get top 5 users based on order count
top(5, users.name, by: count(orders.id))

Get top 5 users using dense ranking
top(5, users.name, by: count(orders.id), logic: 'dense')

Average age of top 5 users by order count
top(5, users.name, by: count(orders.id))
| select(average(users.age))

Inputs

n (Number: required): The number of top values to return.
dimension (Dimension: required): The dimension used to determine the top values.
by (Any: required): The metric to rank by.
logic (Text: optional): The ranking logic ('skip' or 'dense'). Default is 'skip'.

Output

Table: A new table with one row for each top value of the specified dimension.

Learn more

top (AQL Doc)

bottom()

Syntax

bottom(n, dimension, by: metric, [logic])

Description

Returns the bottom N values of a specified dimension based on a metric. The bottom function sorts rows in ascending order by the specified metric. By default, it uses 'skip' ranking logic (similar to the rank function), but can use 'dense' ranking logic when specified with logic: 'dense'.

Tags

Function, Table Function

Examples

Get bottom 5 users based on order count
bottom(5, users.name, by: count(orders.id))

Get bottom 5 users using dense ranking
bottom(5, users.name, by: count(orders.id), logic: 'dense')

Average age of bottom 5 users by order count
bottom(5, users.name, by: count(orders.id))
| select(average(users.age))

Inputs

n (Number: required): The number of bottom values to return.
dimension (Dimension: required): The dimension used to determine the bottom values.
by (Any: required): The metric to rank by.
logic (Text: optional): The ranking logic ('skip' or 'dense'). Default is 'skip'.

Output

Table: A new table with one row for each bottom value of the specified dimension.

Learn more

bottom (AQL Doc)

Window Function

rank()

Syntax

rank(order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the rank of rows within a partition of a table. Tie values are assigned the same rank. The next rank in the sequence is not consecutive. For example: 1, 1, 3, 4, 4, 6, ...

Tags

Function, Metric Function, Window Function

Examples

Rank by order count in descending order
rank(order: count(orders.id) | desc())

Inputs

order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The rank of the current row within its partition.

Learn more

rank (AQL Doc)

dense_rank()

Syntax

dense_rank(order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the rank of rows within a partition of a table. Tie values are assigned the same rank. The next rank in the sequence is consecutive. For example: 1, 1, 2, 3, 3, 4, ...

Tags

Function, Metric Function, Window Function

Examples

Dense rank by order count in descending order
dense_rank(order: count(orders.id) | desc())

Inputs

order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The dense rank of the current row within its partition.

Learn more

dense_rank (AQL Doc)

percent_rank()

Syntax

percent_rank(order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the relative percentile of a row within a partition of a table. The value is between 0 and 1, inclusive.

Tags

Function, Metric Function, Window Function

Examples

Calculate percent rank by order count in descending order
percent_rank(order: count(orders.id) | desc())

Inputs

order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The percentile rank of the current row within its partition.

Learn more

percent_rank (AQL Doc)

ntile()

Syntax

ntile(ranks, order: order_expr, ..., [partition: partition_expr, ...])

Description

Divides the rows within a partition into a specified number of ranked groups.

Tags

Function, Metric Function, Window Function

Examples

Divide rows into 4 groups based on order count in descending order
ntile(4, order: count(orders.id) | desc())

Inputs

ranks (Number: required): The number of ranked groups to divide the rows into.
order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The rank group (or bucket number) the current row belongs to, between 1 and ranks.

Learn more

ntile (AQL Doc)

next()

Syntax

next(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the value from a following row at a specified offset relative to the current row.

Tags

Function, Metric Function, Window Function, Moving Calculation

Examples

Get the next row's user count in descending order
next(count(users.id), order: count(users.id) | desc())

Inputs

expr (Any: required): The value you want to retrieve from the next row.
offset (Number: optional): The number of rows ahead to look. Default is 1.
order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table. Alias: reset.

Output

Any: The value of the next row at the specified offset relative to the current row.

Learn more

next (AQL Doc)

previous()

Syntax

previous(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the value from a preceding row at a specified offset relative to the current row.

Tags

Function, Metric Function, Window Function, Moving Calculation

Examples

Get the previous row's user count in descending order
previous(count(users.id), order: count(users.id) | desc())

Inputs

expr (Any: required): The value you want to retrieve from the previous row.
offset (Number: optional): The number of rows back to look. Default is 1.
order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table. Alias: reset.

Output

Any: The value of the previous row at the specified offset relative to the current row.

Learn more

previous (AQL Doc)

first_value()

Syntax

first_value(expr, order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the value of an expression from the first row of the window frame.

Tags

Function, Metric Function, Window Function

Examples

Get the order count of the first user
first_value(count(orders.id), order: users.created_at | asc())

Inputs

expr (Any: required): The expression to retrieve the first value from.
order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Any: The value of the expression from the first row of the window frame.

Learn more

first_value (AQL Doc)

last_value()

Syntax

last_value(expr, order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the value of an expression from the last row of the window frame.

Tags

Function, Metric Function, Window Function

Examples

Get the order count of the last user
last_value(count(orders.id), order: users.created_at | asc())

Inputs

expr (Any: required): The expression to retrieve the last value from.
order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Any: The value of the expression from the last row of the window frame.

Learn more

last_value (AQL Doc)

nth_value()

Syntax

nth_value(expr, index, order: order_expr, ..., [partition: partition_expr, ...])

Description

Returns the value of an expression from the Nth row of the window frame, where N is a positive integer.

Tags

Function, Metric Function, Window Function

Examples

Get the order count of the second user
nth_value(count(orders.id), 2, order: users.created_at | asc())

Inputs

expr (Any: required): The expression to retrieve the Nth value from.
index (Number: required): The index of the row from which to retrieve the value.
order (Any: required, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Any: The value of the expression from the Nth row of the window frame.

Learn more

nth_value (AQL Doc)

window_sum()

Syntax

window_sum(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])

Description

Returns the sum of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the running total of user count
window_sum(count(users.id))

Calculate the running total of user count with descending order
window_sum(
  count(users.id),
  order: count(users.id) | desc()
)

Calculate running total of user count within a 5-row sliding window with monthly order
window_sum(
  count(users.id),
  -2..2,
  order: users.created_at | month()
)

Calculate running total of user count by gender within monthly order
window_sum(
  count(users.id),
  order: users.created_at | month(),
  partition: users.gender
)

Inputs

aggregation_expression (Any: required): An aggregation expression to be summed.
range (Range: optional): A range of rows to include in the sum. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table. Alias: reset.

Output

Number: The sum of the rows within the specified range.

Learn more

window_sum (AQL Doc)

window_count()

Syntax

window_count(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])

Description

Returns the count of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the running count of gold membership
window_count(
  case(
    when: sum(orders.value) > 1000000,
    then: 1,
    else: null
  )
)

Calculate the running count of gold membership after descending order total order value
window_count(
  case(
    when: sum(orders.value) > 1000000,
    then: 1,
    else: null
  ),
  order: sum(orders.value) | desc()
)

Inputs

aggregation_expression (Any: required): An aggregation expression that we want to count.
range (Range: optional): A range of rows to include in the count. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The count of the rows within the specified range.

Learn more

window_count (AQL Doc)

window_min()

Syntax

window_min(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])

Description

Returns the min of rows in a range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the running minimum user count
window_min(count(users.id))

Calculate the running minimum user count within descending order window
window_min(
  count(users.id),
  order: count(users.id) | desc()
)

Calculate the running minimum user count within a 5-row sliding window and monthly order
window_min(
  count(users.id),
  -2..2,
  order: users.created_at | month()
)

calculate the running minimum user count by gender within monthly order
window_min(
  count(users.id),
  order: users.created_at | month(),
  partition: users.gender
)

Inputs

aggregation_expression (Any: required): An aggregation expression that we want to find min of.
range (Range: optional): A range of rows to include in the min. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The minimum value within the specified range of rows.

Learn more

window_min (AQL Doc)

window_max()

Syntax

window_max(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])

Description

Returns the maximum value in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the running maximum user count
window_max(count(users.id))

Calculate the running maximum user count within descending order window
window_max(
  count(users.id),
  order: count(users.id) | desc()
)

Calculate the running maximum user count within a 5-row sliding window and monthly order
window_max(
  count(users.id),
  -2..2,
  order: users.created_at | month()
)

calculate the running maximum user count by gender within monthly order
window_max(
  count(users.id),
  order: users.created_at | month(),
  partition: users.gender
)

Inputs

agg_expr (Any: required): An aggregation expression that we want to find max of.
range (Range: optional): A range of rows to include in the max. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The maximum value within the specified range of rows.

Learn more

window_max (AQL Doc)

window_avg()

Syntax

window_avg(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])

Description

Returns the average of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the running average of total order value
window_avg(sum(orders.value))

Inputs

agg_expr (Any: required): An aggregation expression to be averaged.
range (Range: optional): A range of rows to include in the average. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The average of values within the specified range of rows.

Learn more

window_avg (AQL Doc)

window_stdev()

Syntax

window_stdev(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])

Description

Returns the sample standard deviation of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the sample standard deviation of total order value relative to current row
window_stdev(sum(orders.value))

Inputs

agg_expr (Any: required): An aggregation expression that we want to find the (sample) standard deviation of.
range (Range: optional): A range of rows to include in the (sample) standard deviation. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The sample standard deviation of values within the specified range of rows.

Learn more

window_stdev (AQL Doc)

window_stdevp()

Syntax

window_stdevp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])

Description

Returns the population standard deviation of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the population standard deviation of total order value relative to current row
window_stdevp(sum(orders.value))

Inputs

agg_expr (Any: required): An aggregation expression that we want to find the (population) standard deviation of.
range (Range: optional): A range of rows to include in the (population) standard deviation. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The population standard deviation of values within the specified range of rows.

Learn more

window_stdevp (AQL Doc)

window_var()

Syntax

window_var(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])

Description

Returns the sample variance of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the sample variance of total order value relative to current row
window_var(sum(orders.value))

Inputs

agg_expr (Any: required): An aggregation expression that we want to find the (sample) variance of.
range (Range: optional): A range of rows to include in the (sample) variance. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The sample variance of values within the specified range of rows.

Learn more

window_var (AQL Doc)

window_varp()

Syntax

window_varp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])

Description

Returns the population variance of values in rows within a specified range relative to the current row.

Tags

Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation

Examples

Calculate the population variance of total order value relative to current row
window_varp(sum(orders.value))

Inputs

agg_expr (Any: required): An aggregation expression that we want to find the (population) variance of.
range (Range: optional): A range of rows to include in the (population) variance. Default is ..0 if order is specified, else ...
order (Any: optional, repeatable): A field used for ordering rows within partitions.
partition (Any: optional, repeatable): A field used for partitioning the table.

Output

Number: The population variance of values within the specified range of rows.

Learn more

window_varp (AQL Doc)

Relationship Function

with_relationships()

Syntax

with_relationships(metric, relationship1, [relationship2, ...])

metric | with_relationships(relationship1, [relationship2, ...])

Description

Calculates a metric using specific relationships that are not active by default. The with_relationships function activates inactive model relationships for a specific metric calculation. It is commonly used with role-playing dimensions or fact constellation schemas and enables metrics that require specific join paths.

Tags

Function, Metric Function, Relationship Function

Examples

Count of orders delivered
count(orders.id)
| with_relationships(orders.delivered_at > dim_dates.date)

Calculate the total order value considering both order and delivery dates
sum(orders.value)
| with_relationships(
  orders.created_at > dim_dates.date,
  orders.delivered_at > dim_dates.date
)

Inputs

metric (Any: required): The original metric to which the specified relationship(s) will be applied.
relationship1 (Relationship: required): The first relationship to apply to the metric calculation.
[relationship2, ...] (Relationship: optional, repeatable): Additional relationships to apply.

Output

Any: The original metric calculation using specified relationships.

Learn more

with_relationships (AQL Doc)

Null/Zero Handling Function

coalesce()

Syntax

coalesce(val1, val2, ...)

Description

Returns the first non-null value in a list of expressions. This function is commonly used to handle missing data by providing fallback values.

Tags

Function, Null/Zero Handling Function

Examples

Select the first available payment value
coalesce(users.yearly_payment, users.quarterly_payment, 0)

Inputs

val1 (Any: required): The first value to check.
val2 (Any: optional, repeatable): Subsequent values to check if previous values are null.

Output

Any: The first non-null value in the arguments.

Learn more

coalesce (AQL Doc)

nullif()

Syntax

nullif(val1, val2)

Description

Returns NULL if two expressions are equal, otherwise returns the first expression. This function is primarily used to avoid divide-by-zero errors in calculations.

Tags

Function, Null/Zero Handling Function

Examples

Handle divide-by-zero in sales calculation with nullif
sales_current / nullif(sales_target, 0)

Inputs

val1 (Any: required): The first value to compare.
val2 (Any: required): The second value to compare.

Output

Any: NULL if values are equal, otherwise val1.

Learn more

nullif (AQL Doc)

safe_divide()

Syntax

safe_divide(dividend, divisor)

Description

Returns the division with a safe mechanism to handle division by zero. Returns NULL if the divisor is zero, preventing potential errors in calculations.

Tags

Function, Null/Zero Handling Function

Examples

Safely calculate average order value
safe_divide(total_sales, count_orders)

Inputs

dividend (Number: required): The number to be divided.
divisor (Number: required): The number to divide by.

Output

Number: The result of the division, or NULL if the divisor is zero.

Learn more

safe_divide (AQl Doc)

Mathematical Function

abs()

Syntax

abs(number)

Description

Returns the absolute value of a number, removing any negative sign and returning the non-negative magnitude.

Tags

Function, Mathematical Function

Examples

Get absolute value of -1 and 5
abs(-1)  // Returns 1
abs(5)   // Returns 5

Inputs

number (Number: required): The number to get the absolute value of.

Output

Number: The non-negative magnitude of the input number.

Learn more

abs (AQL Doc)

sqrt()

Syntax

sqrt(number)

Description

Calculates the square root of a given number.

Tags

Function, Mathematical Function

Examples

Get square root of 9 and 10
sqrt(9)   // Returns 3
sqrt(10)  // Returns approximately 3.1623

Inputs

number (Number: required): The number to calculate the square root of.

Output

Number: The square root of the input number.

Learn more

sqrt (AQL Doc)

ceil()

Syntax

ceil(number)

Description

Returns the smallest integer greater than or equal to the given number.

Tags

Function, Mathematical Function

Examples

Get rounding up to the nearest integer of 1.1 and 1.9
ceil(1.1)  // Returns 2
ceil(1.9)  // Returns 2

Inputs

number (Number: required): The number to round up.

Output

Number: The smallest integer greater than or equal to the input number.

Learn more

ceil (AQL Doc)

floor()

Syntax

floor(number)

Description

Returns the largest integer less than or equal to the given number.

Tags

Function, Mathematical Function

Examples

Get rounding down to the nearest integer of 1.1 and 1.9
floor(1.1)  // Returns 1
floor(1.9)  // Returns 1

Inputs

number (Number: required): The number to round down.

Output

Number: The largest integer less than or equal to the input number.

Learn more

floor (AQL Doc)

round()

Syntax

round(number, [scale])

Description

Rounds a number to a specified number of decimal places. By default, rounds to the nearest whole number.

Tags

Function, Mathematical Function

Examples

Get rounding to the nearest integer
round(1.1)        // Returns 1
round(1.9)        // Returns 2
round(1.12345, 2) // Returns 1.12
round(-1.5)       // Returns -2

Inputs

number (Number: required): The number to round.
scale (Number: optional): The number of decimal places to round to. Default is 0.

Output

Number: The rounded number.

Learn more

round (AQL Doc)

trunc()

Syntax

trunc(number, [scale])

Description

Truncates a number to a specified number of decimal places, removing digits beyond the specified scale.

Tags

Function, Mathematical Function

Examples

Truncate numbers to specified decimal places
trunc(1.1)        // Returns 1
trunc(1.9)        // Returns 1
trunc(1.12345, 2) // Returns 1.12

Inputs

number (Number: required): The number to truncate.
scale (Number: optional): The number of decimal places to keep. Default is 0.

Output

Number: The truncated number.

Learn more

trunc (AQL Doc)

exp()

Syntax

exp(number)

Description

Returns the value of the mathematical constant e (Euler's number) raised to the power of the given number.

Tags

Function, Mathematical Function

Examples

Get exponential function with base e of 1
exp(1)  // Returns approximately 2.718

Inputs

number (Number: required): The exponent to raise e to.

Output

Number: The result of e raised to the given power.

Learn more

exp (AQL Doc)

ln()

Syntax

ln(number)

Description

Calculates the natural logarithm (base e) of a given number.

Tags

Function, Mathematical Function

Examples

Get natural logarithm function with base e of 1 and e
ln(1)    // Returns 0
ln(e())  // Returns 1

Inputs

number (Number: required): The number to calculate the natural logarithm of.

Output

Number: The natural logarithm of the input number.

Learn more

ln (AQL Doc)

log10()

Syntax

log10(number)

Description

Calculates the base 10 logarithm of a given number.

Tags

Function, Mathematical Function

Examples

Get base-10 logarithm of 100 and 10
log10(100)  // Returns 2
log10(10)   // Returns 1

Inputs

number (Number: required): The number to calculate the base 10 logarithm of.

Output

Number: The base 10 logarithm of the input number.

Learn more

log10 (AQL Doc)

log2()

Syntax

log2(number)

Description

Calculates the base 2 logarithm of a given number.

Tags

Function, Mathematical Function

Examples

Get base-2 logarithm of 8 and 16
log2(8)   // Returns 3
log2(16)  // Returns 4

Inputs

number (Number: required): The number to calculate the base 2 logarithm of.

Output

Number: The base 2 logarithm of the input number.

Learn more

log2 (AQL Doc)

pow()

Syntax

pow(base, exponent)

Description

Raises a base number to the power of an exponent.

Tags

Function, Mathematical Function

Examples

Calculate 2^3 and 10^2
pow(2, 3)   // Returns 8
pow(10, 2)  // Returns 100

Inputs

base (Number: required): The base number.
exponent (Number: required): The power to raise the base to.

Output

Number: The result of the base raised to the exponent.

Learn more

pow (AQL Doc)

mod()

Syntax

mod(dividend, divisor)

Description

Returns the remainder of a division operation.

Tags

Function, Mathematical Function

Examples

Get the remainder of 5/2 and 10/3
mod(5, 2)   // Returns 1
mod(10, 3)  // Returns 1

Inputs

dividend (Number: required): The number to be divided.
divisor (Number: required): The number to divide by.

Output

Number: The remainder of the division.

Learn more

mod (AQL Doc)

div()

Syntax

div(dividend, divisor)

Description

Returns the integer quotient of a division operation.

Tags

Function, Mathematical Function

Examples

Get the integer quotient of 5/2 and 10/3
div(5, 2)   // Returns 2
div(10, 3)  // Returns 3

Inputs

dividend (Number: required): The number to be divided.
divisor (Number: required): The number to divide by.

Output

Number: The integer result of the division.

Learn more

div (AQL Doc)

sign()

Syntax

sign(number)

Description

Returns the sign of a number: 1 for positive, -1 for negative, and 0 for zero.

Tags

Function, Mathematical Function

Examples

Get sign of 5, -5, and 0
sign(5)     // Returns 1
sign(-5)    // Returns -1
sign(0)     // Returns 0

Inputs

number (Number: required): The number to determine the sign of.

Output

Number: The sign of the input number (-1, 0, or 1).

Learn more

sign (AQL Doc)

radians()

Syntax

radians(degrees)

Description

Converts degrees to radians.

Tags

Function, Mathematical Function

Examples

Convert 180 degrees to radians
radians(180)  // Returns approximately 3.14159 (π)

Inputs

degrees (Number: required): The angle in degrees to convert.

Output

Number: The angle converted to radians.

Learn more

radians (AQL Doc)

degrees()

Syntax

degrees(radians)

Description

Converts radians to degrees.

Tags

Function, Mathematical Function

Examples

Convert pi radians to degrees
degrees(pi())  // Returns 180

Inputs

radians (Number: required): The angle in radians to convert.

Output

Number: The angle converted to degrees.

Learn more

degrees (AQL Doc)

pi()

Syntax

pi()

Description

Returns the mathematical constant π (pi).

Tags

Function, Mathematical Function

Examples

Get pi number
pi()  // Returns approximately 3.14159

Inputs

Output

Number: The value of π.

Learn more

pi (AQL Doc)

acos()

Syntax

acos(number)

Description

Returns the arccosine (inverse cosine) of a number.

Tags

Function, Mathematical Function

Examples

Get inverse cosine of cosine π
acos(cos(pi()))  // Returns approximately 3.14159

Inputs

number (Number: required): The number to calculate the arccosine of.

Output

Number: The arccosine of the input number.

Learn more

acos (AQL Doc)

asin()

Syntax

asin(number)

Description

Returns the arcsine (inverse sine) of a number.

Tags

Function, Mathematical Function

Examples

asin(sin(pi() / 2))  // Returns approximately 1.5708

Inputs

number (Number: required): The number to calculate the arcsine of.

Output

Number: The arcsine of the input number.

Learn more

asin (AQL Doc)

atan()

Syntax

atan(number)

Description

Returns the arctangent (inverse tangent) of a number.

Tags

Function, Mathematical Function

Examples

Get inverse tangent of tangent π/4
atan(tan(pi() / 4))  // Returns approximately 0.7854

Inputs

number (Number: required): The number to calculate the arctangent of.

Output

Number: The arctangent of the input number.

Learn more

atan (AQL Doc)

atan2()

Syntax

atan2(y, x)

Description

Returns the two-argument arctangent, which computes the angle between the positive x-axis and the point given by the coordinates (x, y).

Tags

Function, Mathematical Function

Examples

Get inverse tangent of coordinates (2, π)
atan2(2, pi())  // Returns a specific angle

Inputs

y (Number: required): The y-coordinate.
x (Number: required): The x-coordinate.

Output

Number: The angle in radians.

Learn more

atan2 (AQL Doc)

cos()

Syntax

cos(number)

Description

Returns the cosine of a number.

Tags

Function, Mathematical Function

Examples

Get cosine of pi
cos(pi())  // Returns -1

Inputs

number (Number: required): The angle in radians.

Output

Number: The cosine of the input number.

Learn more

cos (AQL Doc)

sin()

Syntax

sin(number)

Description

Returns the sine of a number.

Tags

Function, Mathematical Function

Examples

Get sine of pi/2
sin(pi() / 2)  // Returns 1

Inputs

number (Number: required): The angle in radians.

Output

Number: The sine of the input number.

Learn more

sin (AQL Doc)

tan()

Syntax

tan(number)

Description

Returns the tangent of a number.

Tags

Function, Mathematical Function

Examples

Get tan of pi/4
tan(pi() / 4) // => 1

Inputs

number (Number: required): The angle in radians.

Output

Number: The tangent of the input number.

Learn more

tan (AQL Doc)

cot()

Syntax

cot(number)

Description

Returns the cotangent of a number.

Tags

Function, Mathematical Function

Examples

Get cotangent of pi/4
cot(pi() / 4) // => 1

Inputs

number (Number: required): The angle in radians.

Output

Number: The cotangent of the input number.

Learn more

cot (AQL Doc)

AI Function

Platform Availability

AI functions are only available on Databricks and Snowflake data platforms. These functions leverage the native AI capabilities provided by these platforms and are not supported on other database systems.

ai_query()

Syntax

ai_query(model, prompt)

Description

Queries an AI model with a text prompt and returns the generated response.

Tags

Function, AI Function

Examples

Query an AI model
ai_query('databricks-meta-llama-3-3-70b-instruct', 'What is Apache Spark?')

Generate insights from data
orders
| group(orders.product_category)
| select(
    category: orders.product_category,
    total_sales: sum(orders.revenue),
    ai_insight: ai_query('gpt-4', concat('Analyze sales trend for ', orders.product_category))
  )

Inputs

model (Text: required): The AI model to use
prompt (Text: required): The text prompt for the AI model

Output

Text: The AI-generated response

Learn more

ai_query (AQL Doc)

ai_similarity()

Syntax

ai_similarity(text1, text2)

Description

Calculates the semantic similarity between two text strings.

Tags

Function, AI Function

Examples

Compare text similarity
ai_similarity('Apache Spark', 'Apache Spark') // => 1.0

Find similar products
products
| select(
    product_name: products.name,
    similarity_score: ai_similarity(products.description, 'data analytics platform')
  )
| filter(similarity_score > 0.7)

Inputs

text1 (Text: required): The first text to compare
text2 (Text: required): The second text to compare

Output

Number: Similarity score between 0 and 1

Learn more

ai_similarity (AQL Doc)

ai_classify()

Syntax

ai_classify(text, ...categories)

Description

Classifies text into one of the provided categories using AI.

Tags

Function, AI Function

Examples

Classify customer feedback
ai_classify('My password is leaked.', 'urgent', 'not urgent') // => 'urgent'

Categorize support tickets
support_tickets
| select(
    ticket_id: support_tickets.id,
    message: support_tickets.message,
    category: ai_classify(support_tickets.message, 'technical', 'billing', 'general')
  )

Inputs

text (Text: required): The text to classify
categories (Text: required): Variable number of category options

Output

Text: The selected category

Learn more

ai_classify (AQL Doc)

ai_summarize()

Syntax

ai_summarize(content)

Description

Generates a concise summary of the provided text content using AI.

Tags

Function, AI Function

Examples

Summarize long text
ai_summarize('Long article about climate change...')

Summarize customer reviews
product_reviews
| group(product_reviews.product_id)
| select(
    product_id: product_reviews.product_id,
    review_summary: ai_summarize(string_agg(product_reviews.review_text, sep: ';'))
  )

Inputs

content (Text: required): The text content to summarize

Output

Text: The generated summary

Learn more

ai_summarize (AQL Doc)

Miscellaneous Function

cast()

Syntax

cast(expr, type)

Description

Returns the input value casted to the specified data type.

Tags

Function, Miscellaneous Function

Examples

Convert 2000 in string to integer
cast('2000', 'int')

Inputs

expr (Any: required): The expression to cast.
type (Text: required): The target data type. Valid options include: 'text', 'number', 'int'/'integer', 'date', 'datetime', 'truefalse'.

Output

Any: the value cast to the specified type.

Learn more

cast (AQL Doc)

concat()

Syntax

concat(text, [text ...])

Description

Returns the concatenated string of multiple strings.

Tags

Function, Miscellaneous Function

Examples

Concatenate strings to Hello World
concat('Hello', ' ', 'World')

Inputs

text (Text: required, repeatable): The strings to concatenate.

Output

Text: The concatenated string.

Learn more

concat (AQL Doc)

find()

Syntax

find(text, substring)

Description

Returns the 1-based index of the first occurrence of a substring within a text string. Returns 0 if the substring is not found.

Tags

Function, Miscellaneous Function

Examples

Find the position of 'World' in 'Hello World'
find('Hello World', 'World')  // Returns 7

Find the position of 'o' in 'Hello World'
find('Hello World', 'o')  // Returns 5

Find the position of 'Universe' in 'Hello World'
find('Hello World', 'Universe')  // Returns 0

Inputs

text (Text: required): The text string to search within.
substring (Text: required): The substring to search for.

Output

Number: The 1-based index of the first occurrence of the substring, or 0 if not found.

Aliases

find_index()

Learn more

find (AQL Doc)

left()

Syntax

left(text, length)

Description

Returns the leftmost characters of a text string, up to the specified length.

Tags

Function, Miscellaneous Function

Examples

Get the first 5 characters of 'Hello World'
left('Hello World', 5)  // Returns 'Hello'

Get the first 10 characters of 'Hello'
left('Hello', 10)  // Returns 'Hello'

Inputs

text (Text: required): The text string to extract from.
length (Number: required): The number of characters to extract from the left.

Output

Text: The leftmost characters of the text string.

Learn more

left (AQL Doc)

right()

Syntax

right(text, length)

Description

Returns the rightmost characters of a text string, up to the specified length.

Tags

Function, Miscellaneous Function

Examples

Get the last 5 characters of 'Hello World'
right('Hello World', 5)  // Returns 'World'

Get the last 10 characters of 'Hello'
right('Hello', 10)  // Returns 'Hello'

Inputs

text (Text: required): The text string to extract from.
length (Number: required): The number of characters to extract from the right.

Output

Text: The rightmost characters of the text string.

Learn more

right (AQL Doc)

mid()

Syntax

mid(text, start, length)

Description

Extracts a substring of a specified length from a text string, starting at a given position (1-based).

Tags

Function, Miscellaneous Function

Examples

Extract 'World' from 'Hello World'
mid('Hello World', 7, 5)  // Returns 'World'

Extract 'llo' from 'Hello World' starting from position 3
mid('Hello World', 3, 3)  // Returns 'llo'

Inputs

text (Text: required): The text string to extract from.
start (Number: required): The starting position of the substring (1-based).
length (Number: required): The length of the substring to extract.

Output

Text: The extracted substring.

Learn more

mid (AQL Doc)

len()

Syntax

len(text)

Description

Returns the length of a text string (number of characters).

Tags

Function, Miscellaneous Function

Examples

Get the length of 'Hello World'
len('Hello World')  // Returns 11

Get the length of an empty string
len('')  // Returns 0

Inputs

text (Text: required): The text string to calculate the length of.

Output

Number: The length of the text string.

Learn more

len (AQL Doc)

lower()

Syntax

lower(text)

Description

Converts a text string to lowercase.

Tags

Function, Miscellaneous Function

Examples

Convert 'Hello World' to lowercase
lower('Hello World')  // Returns 'hello world'

Convert 'HELLO' to lowercase
lower('HELLO')  // Returns 'hello'

Inputs

text (Text: required): The text string to convert.

Output

Text: The lowercase version of the text string.

Learn more

lower (AQL Doc)

upper()

Syntax

upper(text)

Description

Converts a text string to uppercase.

Tags

Function, Miscellaneous Function

Examples

Convert 'Hello World' to uppercase
upper('Hello World')  // Returns 'HELLO WORLD'

Convert 'hello' to uppercase
upper('hello')  // Returns 'HELLO'

Inputs

text (Text: required): The text string to convert.

Output

Text: The uppercase version of the text string.

Learn more

upper (AQL Doc)

trim()

Syntax

trim(text)

Description

Removes leading and trailing whitespace from a text string.

Tags

Function, Miscellaneous Function

Examples

Trim whitespace from '  Hello World  '
trim('  Hello World  ')  // Returns 'Hello World'

Trim whitespace from ' Hello '
trim(' Hello ')  // Returns 'Hello'

Inputs

text (Text: required): The text string to trim.

Output

Text: The text string with leading and trailing whitespace removed.

Learn more

trim (AQL Doc)

ltrim()

Syntax

ltrim(text)

Description

Removes leading whitespace from a text string.

Tags

Function, Miscellaneous Function

Examples

lTrim whitespace from '  Hello World'
ltrim('  Hello World')  // Returns 'Hello World'

lTrim whitespace from ' Hello '
ltrim(' Hello ')  // Returns 'Hello '

Inputs

text (Text: required): The text string to trim.

Output

Text: The text string with leading whitespace removed.

Learn more

ltrim (AQL Doc)

rtrim()

Syntax

rtrim(text)

Description

Removes trailing whitespace from a text string.

Tags

Function, Miscellaneous Function

Examples

rTrim whitespace from 'Hello World  '
rtrim('Hello World  ')  // Returns 'Hello World'

rTrim whitespace from ' Hello '
rtrim(' Hello ')  // Returns ' Hello'

Inputs

text (Text: required): The text string to trim.

Output

Text: The text string with trailing whitespace removed.

Learn more

rtrim (AQL Doc)

lpad()

Syntax

lpad(text, length, pad_string)

Description

Pads the left side of a text string with a specified pad string until it reaches the specified length. If the text is already longer than the length it will be truncated.

Tags

Function, Miscellaneous Function

Examples

Pad 'Hello' to length 10 with ' '
lpad('Hello', 10, ' ')  // Returns '     Hello'

Pad 'Hello' to length 10 with 'abc'
lpad('Hello', 10, 'abc')  // Returns 'abcabHello'

Pad 'Hello World' to length 10 with '0'
lpad('Hello World', 10, '0')  // Returns 'Hello Worl'

Inputs

text (Text: required): The text string to pad.
length (Number: required): The desired length of the padded string.
pad_string (Text: required): The string to use for padding.

Output

Text: The padded text string.

Learn more

lpad (AQL Doc)

rpad()

Syntax

rpad(text, length, pad_string)

Description

Pads the right side of a text string with a specified pad string until it reaches the specified length. If the text is already longer than the length it will be truncated.

Tags

Function, Miscellaneous Function

Examples

Pad 'Hello' to length 10 with ' '
rpad('Hello', 10, ' ')  // Returns 'Hello     '

Pad 'Hello' to length 10 with 'abc'
rpad('Hello', 10, 'abc')  // Returns 'Helloabcab'

Pad 'Hello World' to length 10 with '0'
rpad('Hello World', 10, '0')  // Returns 'Hello Worl'

Inputs

text (Text: required): The text string to pad.
length (Number: required): The desired length of the padded string.
pad_string (Text: required): The string to use for padding.

Output

Text: The padded text string.

Learn more

rpad (AQL Doc)

replace()

Syntax

replace(text, old_substring, new_substring)

Description

Replaces all occurrences of a substring within a text string with a new substring.

Tags

Function, Miscellaneous Function

Examples

Replace 'World' with 'Universe' in 'Hello World'
replace('Hello World', 'World', 'Universe')  // Returns 'Hello Universe'

Replace all 'o' with '0' in 'Hello World'
replace('Hello World', 'o', '0')  // Returns 'Hell0 W0rld'

Inputs

text (Text: required): The text string to perform the replacement in.
old_substring (Text: required): The substring to replace.
new_substring (Text: required): The substring to replace with.

Output

Text: The text string with the replacements made.

Learn more

replace (AQL Doc)

split_part()

Syntax

split_part(text, delimiter, part_number)

Description

Splits a text string into parts based on a delimiter and returns the specified part (1-based).

Tags

Function, Miscellaneous Function

Examples

Get the second part of 'apple,banana,cherry' split by ','
split_part('apple,banana,cherry', ',', 2)  // Returns 'banana'

Get the first part of 'Hello World' split by ' '
split_part('Hello World', ' ', 1)  // Returns 'Hello'

Inputs

text (Text: required): The text string to split.
delimiter (Text: required): The delimiter to split the string by.
part_number (Number: required): The 1-based index of the part to return.

Output

Text: The specified part of the split string.

Learn more

split_part (AQL Doc)

regexp_extract()

Syntax

regexp_extract(text, regex, [occurrence], [group: _group], [flags: _flags])

Description

Extracts a substring from a text string that matches a regular expression pattern.

Tags

Function, Miscellaneous Function

Examples

Extract a number from a string
regexp_extract('Product123', '[0-9]+')  // Returns '123'

Extract the second word from a string
regexp_extract('Hello World Example', '\\w+', 2) // Returns 'World'

Extract text with case-insensitive match
regexp_extract('Hello World', 'hello', flags: 'i') // Returns 'Hello'

Extract a substring with a capture group
regexp_extract('Product123.3', '(\\d+)\\.\\d+', group: 1) // Returns '123'

Inputs

text (Text: required): The text string to search within.
regex (Text: required): The regular expression pattern to match. (exact regex syntax depends on the database)
occurrence (Number: optional): The occurrence of the matching pattern to extract. (1-based index)
group (Number: optional): The capture group to extract from the matched occurrence. (Bigquery does not support this)
flags (Text: optional): Flags to modify the behavior of the regular expression matching. (supported flags depends on the database)

Output

Text: The extracted substring, or NULL if no match is found.

Learn more

regexp_extract (AQL Doc)

regexp_like()

Syntax

regexp_like(text, regex, [flags: _flags])

Description

Checks if a text string matches a regular expression pattern.

Tags

Function, Miscellaneous Function

Examples

Check if a string contains a number
regexp_like('Product123', '[0-9]+')  // Returns true

Check if a string starts with 'hello' (case insensitive)
regexp_like('Hello World', '^hello', flags: 'i')  // Returns true

Inputs

text (Text: required): The text string to search within.
regex (Text: required): The regular expression pattern to match. (exact regex syntax depends on the database)
flags (Text: optional): Flags to modify the behavior of the regular expression matching. (supported flags depends on the database)

Output

Truefalse: True if the text string matches the regular expression pattern, otherwise false.

Learn more

regexp_like (AQL Doc)

regexp_replace()

Syntax

regexp_replace(text, regex, substitute, [flags: _flags])

Description

Replaces substrings in a text that match a regular expression pattern with a specified replacement text.

Tags

Function, Miscellaneous Function

Examples

Replace all numbers in a text with 'X'
regexp_replace('Product123', '[0-9]+', 'X')  // Returns 'ProductX'

Remove redundant whitespace from a text
regexp_replace('Hello   World', '\\s+', ' ')  // Returns 'Hello World'

Swap the first and last name
regexp_replace('John Doe', '(\\w+) (\\w+)', '\\2, \\1')  // Returns 'Doe, John'

Inputs

text (Text: required): The text string to perform the replacement in.
regex (Text: required): The regular expression pattern to match. (exact regex syntax depends on the database)
substitute (Text: required): The replacement string. You can use backreferences like \\1 or $1 (depending on the specific database) to refer to captured groups in the regex.
flags (Text: optional): Flags to modify the behavior of the regular expression matching. (supported flags depends on the database)

Output

Text: The text with the replacements made.

Learn more

regexp_replace (AQL Doc)

is_at_level()

Syntax

is_at_level(dimension)

Description

Returns true if the specified dimension is active in the Level of Detail (LoD) context, else false.

Tags

Function, Miscellaneous Function

Examples

Calculate sales ratio by city or country level of detail
case(
  when: is_at_level(cities.name),
  then: sum(sales.amount) / (sum(sales.amount) | of_all(cities.name)),
  when: is_at_level(countries.name),
  then: sum(sales.amount) / (sum(sales.amount) | of_all(countries.name)),
  else: 1
)

Inputs

dimension (Dimension: required): The dimension to test.

Output

Truefalse: Whether the dimension is active in the current LoD context or not

Learn more

is_at_level (AQL Doc)

Aggregate Function​

count()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

count_if()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

count_distinct()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

average()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Aliases​

Learn more​

min()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

max()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

sum()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

median()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

stdev()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

stdevp()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Aggregate Function

count()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

count_if()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

count_distinct()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

average()

Syntax

Description

Tags

Examples

Inputs

Output

Aliases

Learn more

min()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

max()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

sum()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

median()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

stdev()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

stdevp()

Syntax

Description

Tags

Examples

Inputs