Semantic SQL Reference

Complete reference for the SQL dialect supported by the Coginiti semantic layer. The semantic layer uses Apache DataFusion as its query engine and translates queries to platform-specific SQL for execution on your data warehouse.

Overview

The semantic layer supports read-only analytical queries using standard SQL syntax. Queries written against semantic entities are analyzed, optimized, and translated to the native SQL dialect of your target platform (PostgreSQL, BigQuery, Snowflake, Redshift, or Yellowbrick).

Key characteristics:

Read-only: Only SELECT queries are supported. No DDL or DML statements.
Cross-platform: Queries are translated to the SQL dialect of your connected data platform.
Semantic-aware: Measure columns must be wrapped in the MEASURE() function for aggregation.
Validation: The query engine validates cross-platform compatibility and rejects constructs that cannot reliably translate across all supported platforms.

Statements

SELECT

The SELECT statement retrieves data from semantic entities.

SELECT column1, column2, MEASURE(measure_column)
FROM entity_name
WHERE condition
GROUP BY column1, column2
HAVING MEASURE(measure_column) > threshold
ORDER BY column1
LIMIT count OFFSET skip

All standard SELECT clauses are supported: FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, and OFFSET.

WITH (Common table expressions)

Common table expressions (CTEs) define named temporary result sets that can be referenced within a query.

WITH monthly_sales AS (
    SELECT product_name, MEASURE(unit_quantity) AS total_qty
    FROM sales_detail
    GROUP BY product_name
)
SELECT product_name, total_qty
FROM monthly_sales
WHERE total_qty > 100
ORDER BY total_qty DESC

Multiple CTEs

WITH
    top_products AS (
        SELECT product_name, MEASURE(unit_quantity) AS qty
        FROM sales_detail
        GROUP BY product_name
    ),
    filtered AS (
        SELECT product_name, qty
        FROM top_products
        WHERE qty > 50
    )
SELECT * FROM filtered

CTEs can reference previously defined CTEs in the same WITH clause. A CTE can be referenced multiple times in the final query.

Column renaming

WITH renamed(name, quantity) AS (
    SELECT product_name, MEASURE(unit_quantity)
    FROM sales_detail
    GROUP BY product_name
)
SELECT name, quantity FROM renamed

CTE limitations

WITH RECURSIVE is not supported.
DISTINCT inside CTEs is not supported.
CTEs do not participate in implicit entity joins. Use explicit JOIN syntax inside CTEs.

UNION ALL

Combine the results of two or more queries. All queries must return the same number of columns with compatible types.

WITH combined AS (
    SELECT product_name, MEASURE(unit_quantity) AS qty
    FROM sales_detail
    WHERE product_key = 'PROD001'
    GROUP BY product_name

    UNION ALL

    SELECT product_name, MEASURE(unit_quantity) AS qty
    FROM sales_detail
    WHERE product_key = 'PROD002'
    GROUP BY product_name
)
SELECT * FROM combined

Clauses

FROM and JOIN

Implicit joins

When you reference columns from multiple entities in a query, the semantic layer automatically adds joins conditions using relationships defined in the semantic model.

SELECT product_name, MEASURE(unit_quantity) AS qty
FROM sales_detail_fact
JOIN dim_product
GROUP BY product_name

Explicit joins

Use explicit JOIN syntax when you need control over join conditions.

SELECT
  p.product_name,
  MEASURE(sd.unit_quantity) AS qty
FROM sales_detail_fact AS sd
INNER JOIN dim_product AS p
  ON p.product_key = sd.product_key
GROUP BY p.product_name

Supported join types

Join type	Syntax	Description
Inner join	`INNER JOIN` or `JOIN`	Only rows with matches in both tables
Left join	`LEFT JOIN` or `LEFT OUTER JOIN`	All rows from the left table, matching rows from the right
Right join	`RIGHT JOIN` or `RIGHT OUTER JOIN`	All rows from the right table, matching rows from the left
Full outer join	`FULL OUTER JOIN`	All rows from both tables
Cross join	`CROSS JOIN`	Cartesian product of both tables

Join conditions

-- Simple equality
ON t2.key = t1.key

-- Multiple conditions
ON t2.key = t1.key AND t2.status = 'active'

-- OR conditions
ON t2.key = t1.key OR t2.alternate_key = t1.key

Derived tables

You can use subqueries in the FROM clause as derived tables.

SELECT t.product_name, t.total_qty
FROM (
    SELECT product_name, MEASURE(unit_quantity) AS total_qty
    FROM sales_detail
    GROUP BY product_name
) AS t
WHERE t.total_qty > 100

Derived tables can be nested and combined with explicit joins.

WHERE

The WHERE clause filters rows based on conditions.

Comparison operators

Operator	Description	Example
`=`	Equal	`WHERE status = 'active'`
`!=` or `<>`	Not equal	`WHERE status != 'inactive'`
`>`	Greater than	`WHERE amount > 100`
`>=`	Greater than or equal	`WHERE amount >= 100`
`<`	Less than	`WHERE amount < 100`
`<=`	Less than or equal	`WHERE amount <= 100`

Logical operators

Operator	Description	Example
`AND`	Both conditions must be true	`WHERE a > 1 AND b < 10`
`OR`	At least one condition must be true	`WHERE a = 1 OR a = 2`
`NOT`	Negates a condition	`WHERE NOT status = 'inactive'`

Use parentheses to control evaluation order:

WHERE (status = 'active' OR status = 'pending') AND amount > 100

NULL predicates

WHERE column IS NULL
WHERE column IS NOT NULL

Range predicates

WHERE amount BETWEEN 100 AND 500
WHERE amount NOT BETWEEN 100 AND 500

BETWEEN is inclusive on both ends. Works with integers, dates, timestamps, and strings.

Set predicates

WHERE status IN ('active', 'pending', 'review')
WHERE status NOT IN ('inactive', 'deleted')

Pattern matching

WHERE name LIKE 'John%'          -- Starts with "John"
WHERE name LIKE '%smith'         -- Ends with "smith"
WHERE name LIKE '%data%'         -- Contains "data"
WHERE name NOT LIKE 'test%'      -- Does not start with "test"

The % wildcard matches any sequence of characters. The _ wildcard matches a single character.

ILIKE (case-insensitive LIKE) is not supported. Use UPPER() or LOWER() with LIKE instead:

-- Instead of: WHERE name ILIKE '%john%'
WHERE UPPER(name) LIKE UPPER('%john%')

Boolean predicates

WHERE is_active IS TRUE
WHERE is_active IS NOT TRUE
WHERE is_active IS FALSE
WHERE is_active IS NOT FALSE

GROUP BY

Group rows by one or more columns for aggregation.

-- By column name
SELECT product_name, MEASURE(unit_quantity) AS qty
FROM sales_detail
GROUP BY product_name

-- By column position
SELECT product_name, MEASURE(unit_quantity) AS qty
FROM sales_detail
GROUP BY 1

-- Multiple columns
SELECT product_name, region, MEASURE(unit_quantity) AS qty
FROM sales_detail
GROUP BY product_name, region

HAVING

Filter groups after aggregation.

SELECT product_name, MEASURE(unit_quantity) AS qty
FROM sales_detail
GROUP BY product_name
HAVING MEASURE(unit_quantity) > 100

HAVING supports the same operators as WHERE but applies to aggregated results:

HAVING MEASURE(qty) > 3 AND MEASURE(qty) < 7
HAVING product_key IN ('PROD001', 'PROD002')

ORDER BY

Sort results by one or more columns.

-- By column name
ORDER BY product_name ASC

-- Descending
ORDER BY total_qty DESC

-- Multiple columns
ORDER BY region ASC, total_qty DESC

-- By column position
ORDER BY 1, 2 DESC

The default sort direction is ASC (ascending).

LIMIT and OFFSET

Restrict the number of rows returned.

-- Return first 10 rows
LIMIT 10

-- Skip 20 rows, return next 10
LIMIT 10 OFFSET 20

LIMIT 0 returns an empty result set. If the limit exceeds the total row count, all rows are returned.

Operators

Arithmetic operators

Operator	Description	Example
`+`	Addition	`price + tax`
`-`	Subtraction	`revenue - cost`
`*`	Multiplication	`qty * price`
`/`	Division	`total / count`
`%`	Modulo	`value % 2`

String concatenation

SELECT first_name || ' ' || last_name AS full_name

The || operator concatenates strings. Use the CONCAT() function for concatenating more than two values.

The MEASURE() function

The MEASURE() function is a semantic layer extension that wraps measure columns for aggregation. When a column is defined as a measure in the semantic model, it must be accessed through MEASURE().

Syntax

MEASURE(entity.measure_column)

Behavior

The MEASURE() function applies the aggregation defined in the semantic model (sum, count, avg, min, max, or count distinct) to the specified measure column.

-- If unit_quantity is defined with aggregation_type = "sum":
SELECT MEASURE(unit_quantity)
FROM sales_detail
-- Translates to: SELECT SUM(unit_quantity) FROM ...

Rules

Measure columns must be wrapped in MEASURE(). Using a measure column directly produces an error.
MEASURE() can be used in SELECT, HAVING, and ORDER BY clauses.
When GROUP BY is present, non-aggregated columns must appear in the GROUP BY clause.

-- Valid: measure wrapped in MEASURE()
SELECT product_name, MEASURE(unit_quantity) AS qty
FROM sales_detail
GROUP BY product_name

-- Invalid: measure used without MEASURE()
SELECT product_name, unit_quantity
FROM sales_detail
GROUP BY product_name
-- Error: Cannot mix dimensions and measures without aggregation

For details on defining measures in the semantic model, see the Semantic Model Reference.

Data types

The semantic layer supports the following data types.

Semantic type	SQL type	Description
`integer`	`INT` / `BIGINT`	64-bit signed integer
`float`	`DOUBLE` / `FLOAT`	64-bit floating point
`number`	`DOUBLE` / `FLOAT`	Synonym for float
`decimal`	`DECIMAL(38, 10)`	Fixed-precision decimal
`text`	`VARCHAR`	Variable-length string
`date`	`DATE`	Calendar date
`datetime`	`TIMESTAMP`	Date and time (millisecond precision)
`bool`	`BOOLEAN`	True or false

Timestamp behavior

Timestamp values returned by the semantic layer have two behaviors to be aware of:

Precision: Timestamp values have millisecond precision. Values with microsecond or nanosecond precision in the source data are truncated to milliseconds.
Timezone handling: All timezone-aware timestamps are converted to UTC. For example, a value of 2024-01-15T21:00:00+02:00 is returned as 2024-01-15T19:00:00Z.

Type casting

-- Explicit cast
CAST(42 AS VARCHAR)
CAST('42' AS INTEGER)
CAST('2023-06-15' AS DATE)

-- Safe cast (returns NULL on failure instead of error)
TRY_CAST('not_a_number' AS INTEGER)  -- Returns NULL
TRY_CAST('123' AS INTEGER)           -- Returns 123

Scalar functions

Math functions

Function	Description	Example	Result
`ABS(x)`	Absolute value	`ABS(-42)`	`42`
`CEIL(x)`	Round up to nearest integer	`CEIL(1.7)`	`2`
`FLOOR(x)`	Round down to nearest integer	`FLOOR(1.7)`	`1`
`ROUND(x, d)`	Round to `d` decimal places	`ROUND(3.14159, 2)`	`3.14`
`TRUNC(x, d)`	Truncate to `d` decimal places	`TRUNC(3.14159, 2)`	`3.14`
`SIGNUM(x)`	Sign of number (-1, 0, or 1)	`SIGNUM(-5)`	`-1`
`POWER(base, exp)`	Exponentiation	`POWER(2, 10)`	`1024`
`SQRT(x)`	Square root	`SQRT(144)`	`12`
`EXP(x)`	Euler's number raised to `x`	`EXP(0)`	`1`
`LN(x)`	Natural logarithm	`LN(1)`	`0`
`LOG10(x)`	Base-10 logarithm	`LOG10(1000)`	`3`
`LOG2(x)`	Base-2 logarithm	`LOG2(1024)`	`10`
`LOG(base, x)`	Logarithm with custom base	`LOG(10, 1000)`	`3`
`PI()`	Pi constant	`PI()`	`3.14159...`
`GREATEST(a, b, ...)`	Largest value among arguments	`GREATEST(1, 5, 3)`	`5`
`LEAST(a, b, ...)`	Smallest value among arguments	`LEAST(1, 5, 3)`	`1`
`GCD(a, b)`	Greatest common divisor	`GCD(48, 18)`	`6`
`LCM(a, b)`	Least common multiple	`LCM(4, 6)`	`12`
`FACTORIAL(n)`	Factorial	`FACTORIAL(5)`	`120`

Trigonometric functions

Function	Description	Example	Result
`SIN(x)`	Sine (radians)	`SIN(0)`	`0`
`COS(x)`	Cosine (radians)	`COS(0)`	`1`
`TAN(x)`	Tangent (radians)	`TAN(0)`	`0`

String functions

Function	Description	Example	Result
`UPPER(s)`	Convert to uppercase	`UPPER('hello')`	`HELLO`
`LOWER(s)`	Convert to lowercase	`LOWER('HELLO')`	`hello`
`INITCAP(s)`	Capitalize first letter of each word	`INITCAP('hello world')`	`Hello World`
`LENGTH(s)`	String length	`LENGTH('hello')`	`5`
`CHARACTER_LENGTH(s)`	String length (synonym)	`CHARACTER_LENGTH('hello')`	`5`
`BIT_LENGTH(s)`	Length in bits	`BIT_LENGTH('A')`	`8`
`CONCAT(s1, s2, ...)`	Concatenate strings	`CONCAT('foo', 'bar')`	`foobar`
`CONCAT_WS(sep, s1, s2, ...)`	Concatenate with separator	`CONCAT_WS('-', 'a', 'b', 'c')`	`a-b-c`
`SUBSTR(s, start, len)`	Extract substring	`SUBSTR('abcdef', 2, 3)`	`bcd`
`LEFT(s, n)`	First `n` characters	`LEFT('abcdef', 3)`	`abc`
`RIGHT(s, n)`	Last `n` characters	`RIGHT('abcdef', 3)`	`def`
`TRIM(s)`	Remove leading/trailing whitespace	`TRIM(' hello ')`	`hello`
`LTRIM(s, chars)`	Remove leading characters	`LTRIM('xxhello', 'x')`	`hello`
`RTRIM(s, chars)`	Remove trailing characters	`RTRIM('helloxx', 'x')`	`hello`
`LPAD(s, width, pad)`	Left-pad to width	`LPAD('hi', 5, '*')`	`***hi`
`RPAD(s, width, pad)`	Right-pad to width	`RPAD('hi', 5, '*')`	`hi***`
`REPLACE(s, from, to)`	Replace occurrences	`REPLACE('hello', 'l', 'r')`	`herro`
`REVERSE(s)`	Reverse string	`REVERSE('abc')`	`cba`
`REPEAT(s, n)`	Repeat string `n` times	`REPEAT('ab', 3)`	`ababab`
`STARTS_WITH(s, prefix)`	Check if starts with prefix	`STARTS_WITH('abcdef', 'abc')`	`true`
`ENDS_WITH(s, suffix)`	Check if ends with suffix	`ENDS_WITH('abcdef', 'def')`	`true`
`STRPOS(s, substr)`	Position of substring (1-based)	`STRPOS('abcabc', 'bc')`	`2`
`CONTAINS(s, substr)`	Check if contains substring	`CONTAINS('abcdef', 'cd')`	`true`
`ASCII(c)`	ASCII code of character	`ASCII('A')`	`65`
`CHR(code)`	Character from ASCII code	`CHR(65)`	`A`
`SPLIT_PART(s, sep, pos)`	Extract part of delimited string	`SPLIT_PART('a,b,c', ',', 2)`	`b`

Regex functions

Function	Description	Example	Result
`REGEXP_LIKE(s, pattern)`	Test if string matches pattern	`REGEXP_LIKE('abc123', '[0-9]+')`	`true`
`REGEXP_REPLACE(s, pattern, replacement)`	Replace pattern matches	`REGEXP_REPLACE('abc123', '[0-9]+', 'X')`	`abcX`
`REGEXP_COUNT(s, pattern)`	Count pattern matches	`REGEXP_COUNT('banana', 'an')`	`2`

REGEXP_MATCH is not supported due to incompatible return types across platforms.

Date and time functions

Function	Description	Example
`DATE_TRUNC(unit, timestamp)`	Truncate to time unit	`DATE_TRUNC('month', TIMESTAMP '2023-06-15 14:30:00')`
`DATE_PART(unit, timestamp)`	Extract numeric date part	`DATE_PART('year', TIMESTAMP '2023-01-15 10:20:30')`
`EXTRACT(unit FROM timestamp)`	Extract date/time field	`EXTRACT(MONTH FROM TIMESTAMP '2023-06-15 10:00:00')`
`TO_TIMESTAMP(s)`	Parse string to timestamp	`TO_TIMESTAMP('2023-06-15T10:30:00')`
`TO_DATE(s)`	Parse string to date	`TO_DATE('2023-01-02')`
`MAKE_DATE(year, month, day)`	Construct a date	`MAKE_DATE(2023, 6, 15)`
`NOW()`	Current timestamp	`NOW()`
`CURRENT_DATE`	Current date	`CURRENT_DATE`

Supported time units

The following units can be used with DATE_TRUNC, DATE_PART, and EXTRACT:

year, quarter, month, week, day, hour, minute, second, millisecond, microsecond

Conditional functions

Function	Description	Example	Result
`COALESCE(v1, v2, ...)`	First non-NULL value	`COALESCE(NULL, 'fallback')`	`fallback`
`NULLIF(a, b)`	NULL if `a = b`, else `a`	`NULLIF('same', 'same')`	`NULL`
`NVL(v, default)`	`default` if `v` is NULL	`NVL(NULL, 'fallback')`	`fallback`
`NVL2(v, if_not_null, if_null)`	Choose value based on NULL	`NVL2(NULL, 'x', 'y')`	`y`
`IFNULL(v, default)`	`default` if `v` is NULL	`IFNULL(NULL, 42)`	`42`

CASE expressions

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     ELSE default_result
END

Example:

SELECT
    product_name,
    CASE
        WHEN MEASURE(unit_quantity) > 100 THEN 'high'
        WHEN MEASURE(unit_quantity) > 50 THEN 'medium'
        ELSE 'low'
    END AS volume_tier
FROM sales_detail
GROUP BY product_name

Hash functions

Function	Description	Example
`MD5(s)`	MD5 hash of string	`MD5('abc')`

Aggregate functions

Aggregate functions compute a single result from a set of input values. They are used with GROUP BY or over an entire result set.

Function	Description	Example
`SUM(expr)`	Sum of values	`SUM(amount)`
`COUNT(expr)`	Count of non-NULL values	`COUNT(customer_id)`
`COUNT(*)`	Count of all rows	`COUNT(*)`
`COUNT(DISTINCT expr)`	Count of distinct non-NULL values	`COUNT(DISTINCT status)`
`AVG(expr)`	Average of values	`AVG(price)`
`MIN(expr)`	Minimum value	`MIN(created_date)`
`MAX(expr)`	Maximum value	`MAX(amount)`
`STDDEV(expr)`	Sample standard deviation	`STDDEV(score)`
`STDDEV_POP(expr)`	Population standard deviation	`STDDEV_POP(score)`
`STDDEV_SAMP(expr)`	Sample standard deviation (synonym for `STDDEV`)	`STDDEV_SAMP(score)`
`VAR_POP(expr)`	Population variance	`VAR_POP(score)`

All aggregate functions skip NULL values. COUNT(*) is the exception, counting all rows including those with NULLs.

Window functions

Window functions perform calculations across a set of rows related to the current row without collapsing the result set.

Syntax

function_name(args) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column1 [ASC|DESC], ...]
    [frame_clause]
)

Ranking functions

Function	Description
`ROW_NUMBER()`	Unique sequential number for each row in the partition
`RANK()`	Rank with gaps for tied values
`DENSE_RANK()`	Rank without gaps for tied values
`NTILE(n)`	Distribute rows into `n` approximately equal buckets
`PERCENT_RANK()`	Relative rank as a fraction between 0 and 1
`CUME_DIST()`	Cumulative distribution value between 0 and 1

Example:

SELECT
    product_name,
    MEASURE(unit_quantity) AS qty,
    ROW_NUMBER() OVER (ORDER BY MEASURE(unit_quantity) DESC) AS rank
FROM sales_detail
GROUP BY product_name

Function	Description
`LAG(expr [, offset [, default]])`	Value from a previous row. Default offset is 1.
`LEAD(expr [, offset [, default]])`	Value from a following row. Default offset is 1.
`FIRST_VALUE(expr)`	First value in the window frame
`LAST_VALUE(expr)`	Last value in the window frame
`NTH_VALUE(expr, n)`	Nth value in the window frame

Example:

SELECT
    product_name,
    MEASURE(unit_quantity) AS qty,
    LAG(MEASURE(unit_quantity), 1, 0) OVER (ORDER BY product_name) AS prev_qty
FROM sales_detail
GROUP BY product_name

Aggregate functions as window functions

All aggregate functions can be used as window functions with the OVER clause:

SELECT
    product_name,
    MEASURE(unit_quantity) AS qty,
    SUM(MEASURE(unit_quantity)) OVER (ORDER BY product_name) AS running_total,
    AVG(MEASURE(unit_quantity)) OVER () AS overall_avg
FROM sales_detail
GROUP BY product_name

PARTITION BY

Divide rows into groups for independent window calculations:

ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC)

Window frame specification

Control which rows are included in the window calculation:

-- Cumulative (default for ORDER BY)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Entire partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- Sliding window of 3 rows
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Frame boundaries

Boundary	Description
`UNBOUNDED PRECEDING`	First row of the partition
`N PRECEDING`	N rows before the current row
`CURRENT ROW`	The current row
`N FOLLOWING`	N rows after the current row
`UNBOUNDED FOLLOWING`	Last row of the partition

Subqueries

Supported: derived tables

Subqueries in the FROM clause (derived tables) are fully supported:

SELECT t.product_name, t.total_qty
FROM (
    SELECT product_name, MEASURE(unit_quantity) AS total_qty
    FROM sales_detail
    GROUP BY product_name
) AS t
WHERE t.total_qty > 100

Derived tables can be joined with other entities or derived tables:

SELECT p.product_name, t.total_qty
FROM dim_product AS p
LEFT JOIN (
    SELECT product_key, MEASURE(unit_quantity) AS total_qty
    FROM sales_detail
    GROUP BY product_key
) AS t ON t.product_key = p.product_key

Not supported: IN, EXISTS, and scalar subqueries

The following subquery patterns are not supported:

-- NOT SUPPORTED: IN subquery
SELECT * FROM sales_detail
WHERE product_key IN (SELECT product_key FROM dim_product WHERE category = 'Electronics')

-- NOT SUPPORTED: EXISTS subquery
SELECT * FROM sales_detail
WHERE EXISTS (SELECT 1 FROM dim_product WHERE dim_product.key = sales_detail.key)

-- NOT SUPPORTED: Scalar subquery
SELECT (SELECT MAX(unit_quantity) FROM sales_detail) AS max_qty

Workaround: Use derived tables with explicit joins instead:

-- Use a derived table join instead of IN subquery
SELECT sd.*
FROM sales_detail AS sd
JOIN (
    SELECT DISTINCT product_key
    FROM dim_product
    WHERE category = 'Electronics'
) AS p ON p.product_key = sd.product_key

Unsupported statements

The semantic layer is designed for analytical read queries. The following SQL statements are not supported:

Category	Statements
Data modification	`INSERT`, `UPDATE`, `DELETE`, `MERGE`
Data definition	`CREATE TABLE`, `DROP TABLE`, `ALTER TABLE`, `CREATE INDEX`
Transaction control	`BEGIN`, `COMMIT`, `ROLLBACK`
Session control	`SET`, `SHOW`

Platform compatibility

The semantic layer translates queries to platform-specific SQL. The following table documents notable platform differences that the translation layer handles automatically.

Automatic platform translations

Feature	PostgreSQL	BigQuery	Snowflake	Redshift	Yellowbrick
Float casts	`REAL`	Native	Native	`REAL`	`REAL`
Decimal casts	`DECIMAL(p,s)`	`NUMERIC`	`DECIMAL(p,s)`	`DECIMAL(p,s)`	`DECIMAL(p,s)`
`IS TRUE` / `IS FALSE`	Native	Native	`COALESCE` rewrite	Native	Native
`LAG`/`LEAD` with default	Native	Native	Native	`COALESCE` rewrite	Native
`DATE_TRUNC` argument order	Standard	Reversed	Standard	Standard	Standard
Window `RANGE` frames	Supported	Converted to `ROWS`	Converted to `ROWS`	Converted to `ROWS`	Supported
Ranking function frames	Kept	Removed	Removed	Removed	Kept
Timestamp with timezone	Native	Native	`TIMESTAMP_TZ`	Native	Native
Identifier quoting	`"double quotes"`	`backticks`	`"double quotes"`	`"double quotes"`	`"double quotes"`
Derived table aliases	Required	Optional	Required	Required	Required

Function availability by platform

All scalar, aggregate, and window functions listed in this reference are available on all supported platforms.

Examples

Basic aggregation

SELECT
    product_name,
    MEASURE(unit_quantity) AS total_units,
    MEASURE(sales_amount) AS total_sales
FROM sales_detail
GROUP BY product_name
ORDER BY total_sales DESC
LIMIT 10

Filtering with HAVING

SELECT
    product_name,
    MEASURE(unit_quantity) AS total_units
FROM sales_detail
GROUP BY product_name
HAVING MEASURE(unit_quantity) > 100
ORDER BY total_units DESC

Window function with running total

WITH product_sales AS (
    SELECT
        product_name,
        MEASURE(unit_quantity) AS qty
    FROM sales_detail
    GROUP BY product_name
)
SELECT
    product_name,
    qty,
    SUM(qty) OVER (ORDER BY qty DESC) AS running_total,
    RANK() OVER (ORDER BY qty DESC) AS rank
FROM product_sales
ORDER BY rank

Multi-level aggregation with CTEs

WITH daily_sales AS (
    SELECT
        product_name,
        DATE_TRUNC('day', sale_date) AS sale_day,
        MEASURE(unit_quantity) AS daily_qty
    FROM sales_detail
    GROUP BY product_name, DATE_TRUNC('day', sale_date)
),
product_summary AS (
    SELECT
        product_name,
        AVG(daily_qty) AS avg_daily_qty,
        MAX(daily_qty) AS peak_daily_qty,
        COUNT(*) AS active_days
    FROM daily_sales
    GROUP BY product_name
)
SELECT *
FROM product_summary
WHERE avg_daily_qty > 10
ORDER BY avg_daily_qty DESC

Derived table with join

SELECT
    p.product_name,
    t.total_qty,
    t.total_qty * 1.0 / overall.grand_total AS pct_of_total
FROM dim_product AS p
JOIN (
    SELECT product_key, MEASURE(unit_quantity) AS total_qty
    FROM sales_detail
    GROUP BY product_key
) AS t ON t.product_key = p.product_key
CROSS JOIN (
    SELECT MEASURE(unit_quantity) AS grand_total
    FROM sales_detail
) AS overall
ORDER BY t.total_qty DESC

Overview​

Statements​

SELECT​

WITH (Common table expressions)​

Multiple CTEs​

Column renaming​

CTE limitations​

UNION ALL​

Clauses​

FROM and JOIN​

Implicit joins​

Explicit joins​

Supported join types​

Join conditions​

Derived tables​

WHERE​

Comparison operators​

Logical operators​

NULL predicates​

Range predicates​

Set predicates​

Pattern matching​

Boolean predicates​

GROUP BY​

HAVING​

ORDER BY​

LIMIT and OFFSET​

Operators​

Arithmetic operators​

String concatenation​

The MEASURE() function​

Syntax​

Behavior​

Rules​

Data types​

Timestamp behavior​

Type casting​

Scalar functions​

Math functions​

Trigonometric functions​

String functions​

Regex functions​

Date and time functions​

Supported time units​

Conditional functions​

CASE expressions​

Hash functions​

Aggregate functions​

Window functions​

Syntax​

Ranking functions​

Navigation functions​

Aggregate functions as window functions​

PARTITION BY​

Window frame specification​

Frame boundaries​

Subqueries​

Supported: derived tables​

Not supported: IN, EXISTS, and scalar subqueries​

Unsupported statements​

Platform compatibility​

Automatic platform translations​

Function availability by platform​

Examples​

Basic aggregation​

Filtering with HAVING​

Window function with running total​

Multi-level aggregation with CTEs​

Derived table with join​

Overview

Statements

SELECT

WITH (Common table expressions)

Multiple CTEs

Column renaming

CTE limitations

UNION ALL

Clauses

FROM and JOIN

Implicit joins

Explicit joins

Supported join types

Join conditions

Derived tables

WHERE

Comparison operators

Logical operators

NULL predicates

Range predicates

Set predicates

Pattern matching

Boolean predicates

GROUP BY

HAVING

ORDER BY

LIMIT and OFFSET

Operators

Arithmetic operators

String concatenation

The MEASURE() function

Syntax

Behavior

Rules

Data types

Timestamp behavior

Type casting

Scalar functions

Math functions

Trigonometric functions

String functions

Regex functions

Date and time functions

Supported time units

Conditional functions

CASE expressions

Hash functions

Aggregate functions

Window functions

Syntax

Ranking functions

Navigation functions

Aggregate functions as window functions

PARTITION BY

Window frame specification

Frame boundaries

Subqueries

Supported: derived tables

Not supported: IN, EXISTS, and scalar subqueries

Unsupported statements

Platform compatibility

Automatic platform translations

Function availability by platform

Examples

Basic aggregation

Filtering with HAVING

Window function with running total

Multi-level aggregation with CTEs

Derived table with join