NexusQL Language Reference

This document provides the full reference for NexusQL — a custom SQL-like syntax for querying Nexus, the OLAP analytical database provided by Biron.

It focuses on the distinctive features that differ from standard SQL. For standard SQL constructs (e.g., CASE expressions, arithmetic), please consult standard SQL references.

---

1. Overview

NexusQL is designed for analyzing metrics along various dimensions over specific periods. It introduces specialized functions (metric, dimension, refDateDimension) and imposes certain constraints on FROM and date filtering. Subqueries and window functions are supported with some limitations detailed below.

A NexusQL query is always a SELECT statement. No other statement types (INSERT, UPDATE, DELETE, etc.) are supported.

---

2. Basic Query Structure

A straightforward NexusQL query has the following form:

SELECT
    refDateDimension(INTERVAL 1 MONTH) AS date_alias,
    dimension('some_dim') AS dim_alias,
    metric('view.metric_code') AS metric_alias
FROM datamodel
WHERE refDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
  AND dimension('some_dim') = 'some_value'
GROUP BY date_alias, dim_alias
ORDER BY date_alias
LIMIT 100

Rules:

• All non-metric columns or expressions in SELECT must appear in GROUP BY.
• The WHERE clause must include a refDate BETWEEN ... filter.
• At least one metric() must be selected.

---

3. Key Differences from Standard SQL

3.1 The FROM datamodel clause

You must always query from datamodel (i.e., FROM datamodel) when retrieving metrics and dimensions. The datamodel keyword refers to the analytical data model configured in your workspace — you do not reference physical table names directly.

If you need to perform window functions, combine multiple result sets, or do post-processing, you can wrap FROM datamodel queries inside subqueries and use standard SQL in the outer query. See section 7.

3.2 Metrics replace aggregate functions

Standard aggregate functions like SUM, COUNT, or AVG are replaced by the metric() function. Each metric is pre-defined in a view and referenced by <viewCode>.<metricCode>.

SELECT metric('transactions_order_date.billing_with_vat') AS total_billing
FROM datamodel
WHERE refDate BETWEEN '2025-01-01' AND '2025-01-31'

3.3 Dimensions replace column references

To segment or group by a column, use dimension('dimension_code') instead of referencing a column name directly.

SELECT dimension('product_brand') AS brand,
       metric('transactions_order_date.billing_n_products') AS product_count
FROM datamodel
WHERE refDate BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY brand

3.4 Mandatory date filtering

The WHERE clause must contain a refDate BETWEEN ... filter that defines the analysis period:

WHERE refDate BETWEEN '2023-01-01' AND '2023-01-31'

Additional filters on dimensions can be added with AND:

WHERE refDate BETWEEN '2023-01-01' AND '2023-01-31'
  AND dimension('customer_age') > 30

---

4. NexusQL Functions

4.1 metric('<view>.<metric>'[, additionalArgs, ...])

Retrieves a predefined metric from a view.

Mandatory argument: a string '<viewCode>.<metricCode>'.

Optional arguments:

• overrideRefDate('start', 'end') — override the date range for this specific metric (at most once per metric).
• addWhere(<expression>) — apply an additional filter only for this metric’s computation, not the entire query. The expression uses the same syntax as WHERE filters.

SELECT metric('transactions_order_date.billing_ht') AS total_billing
FROM datamodel
WHERE refDate BETWEEN '2025-01-01' AND '2025-01-31'

4.2 dimension('dimension_code')

References a dimension by its code. Used in SELECT, WHERE, GROUP BY, and ORDER BY.

SELECT dimension('order_shipping_address_country') AS country,
       metric('xxx.yyy') AS value
FROM datamodel
WHERE refDate BETWEEN '2023-01-01' AND '2023-02-28'
  AND dimension('customer_age') > 30
GROUP BY country

Dimensions can also be used inside expressions:

SELECT dateDiff('day', dimension('customer_first_order_date'), refDate) AS days_since_first_order,
       metric('xxx.yyy') AS value
FROM datamodel
WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY days_since_first_order

4.3 refDateDimension(INTERVAL 1 <period>)

Generates time segments based on a specified interval. It groups the refDate into regular time buckets.

Allowed periods: DAY, WEEK, MONTH, QUARTER, YEAR.

SELECT refDateDimension(INTERVAL 1 WEEK) AS weekly_date,
       metric('xxx.yyy') AS value
FROM datamodel
WHERE refDate BETWEEN '2023-01-01' AND '2023-06-30'
GROUP BY weekly_date
ORDER BY weekly_date

4.4 overrideRefDate('start', 'end')

When passed as an additional argument to metric(), it replaces the default date range (from WHERE refDate BETWEEN ...) for that specific metric. This is useful for period-over-period comparisons.

SELECT metric('transactions_order_date.billing_ht')                                              AS current_billing,
       metric('transactions_order_date.billing_ht', overrideRefDate('2024-01-01', '2024-01-31')) AS last_year_billing
FROM datamodel
WHERE refDate BETWEEN '2025-01-01' AND '2025-01-31'

4.5 addWhere(<expression>)

Applies an extra condition that affects only the associated metric’s computation, not the entire query. The expression uses the same syntax as a WHERE filter.

SELECT metric('transactions_order_date.billing_ttc',
              addWhere(dimension('customer_age') BETWEEN 25 AND 35)) AS billing_young_customers,
       metric('transactions_order_date.billing_ttc')                 AS billing_all_customers
FROM datamodel
WHERE refDate BETWEEN '2025-01-01' AND '2025-01-31'

---

5. SQL Clauses

5.1 GROUP BY

All selected non-metric expressions must appear in GROUP BY. You can reference them by alias or by repeating the expression.

The GROUP BY ... WITH TOTALS syntax is supported — it adds an extra row with the totals across all groups:

SELECT dimension('product_brand') AS brand,
       metric('xxx.yyy') AS value
FROM datamodel
WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY brand WITH TOTALS

5.2 HAVING

The HAVING clause is supported for filtering on aggregated results. Within HAVING, you can reference selected expressions by their alias:

SELECT dimension('product_brand') AS brand,
       metric('transactions_order_date.billing_ht') AS billing
FROM datamodel
WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY brand
HAVING billing > 1000
ORDER BY billing DESC

5.3 ORDER BY

Sort results by any selected expression. Supports ASC (ascending, default) and DESC (descending):

ORDER BY billing DESC, brand ASC

5.4 LIMIT and OFFSET

Restrict the number of returned rows:

LIMIT 10            -- first 10 rows
LIMIT 10 OFFSET 20  -- 10 rows starting at position 20
LIMIT 20, 10        -- equivalent: offset 20, then 10 rows

---

6. Operators and Expressions

6.1 Comparison operators

Operator	Description
=	Equal
!= or <>	Not equal
>	Greater than
>=	Greater than or equal
<	Less than
<=	Less than or equal

6.2 Logical operators

Operator	Description
AND	Logical AND
OR	Logical OR
XOR	Logical exclusive OR
NOT	Logical negation

6.3 Arithmetic operators

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division

6.4 Pattern matching and set operators

Operator	Description
LIKE	Case-sensitive pattern match (% = any chars, _ = one char)
ILIKE	Case-insensitive pattern match
IN (...)	Membership test
BETWEEN x AND y	Range test (inclusive)

These can all be negated with NOT:

WHERE dimension('status') NOT IN ('cancelled', 'returned')
  AND dimension('name') NOT LIKE '%test%'

6.5 CASE expressions

Standard CASE expressions are supported:

SELECT CASE
           WHEN metric('xxx.yyy') > 1000 THEN 'high'
           WHEN metric('xxx.yyy') > 100  THEN 'medium'
           ELSE 'low'
       END AS category

6.6 String literals

String values are enclosed in single quotes. Use \' or '' to escape a single quote within a string:

WHERE dimension('name') = 'O\'Brien'
WHERE dimension('name') = 'O''Brien'   -- equivalent

6.7 Backtick-quoted identifiers

Identifiers that conflict with reserved words or contain special characters can be enclosed in backticks:

SELECT `select` AS my_alias

6.8 Comments

NexusQL supports SQL comments:

/* This is a block comment */
SELECT metric('xxx.yyy') AS value  -- This is a line comment
FROM datamodel                     # This is also a line comment
WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'

---

7. Subqueries, Joins, and QUALIFY

7.1 Subqueries

You can place a FROM datamodel query inside a subquery to perform post-processing. In the outer query, standard SQL rules apply — you can use standard aggregation functions, HAVING, JOIN clauses, etc.

Each subquery must have an alias:

SELECT brand, product_count
FROM (
    SELECT dimension('product_brand')                           AS brand,
           metric('transactions_order_date.billing_n_products') AS product_count
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY brand
) AS sub
WHERE product_count > 100
ORDER BY product_count DESC

7.2 Joins between subqueries

You can join multiple subqueries using standard join syntax (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN):

SELECT t1.cohort, t2.total_customers, t1.revenue
FROM (
    SELECT dimension('customer_cohort') AS cohort,
           metric('xxx.revenue') AS revenue
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY cohort
) AS t1
LEFT JOIN (
    SELECT dimension('customer_cohort') AS cohort,
           metric('xxx.customer_count') AS total_customers
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY cohort
) AS t2 ON t1.cohort = t2.cohort
ORDER BY t1.cohort

7.3 The values() table function

The values() function allows you to define inline data as a table source, useful for mapping or lookup tables:

SELECT sub.category, mapping.label
FROM (
    SELECT dimension('product_category') AS category,
           metric('xxx.yyy') AS value
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY category
) AS sub
INNER JOIN values(('A', 'Premium'), ('B', 'Standard'), ('C', 'Economy')) AS mapping ON sub.category = mapping.c1

The columns of values() are automatically named c1, c2, c3, etc.

7.4 Window functions

Window functions (e.g., ROW_NUMBER(), RANK(), SUM() OVER(...)) are supported in outer queries — not directly inside a FROM datamodel query.

SELECT brand,
       billing,
       RANK() OVER (ORDER BY billing DESC) AS rank
FROM (
    SELECT dimension('product_brand')                    AS brand,
           metric('transactions_order_date.billing_ht') AS billing
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY brand
) AS sub
ORDER BY rank

Supported window function clauses:

• PARTITION BY — partition rows into groups
• ORDER BY — define ordering within partitions
• ROWS / RANGE frame specifications (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

7.5 QUALIFY clause

The QUALIFY clause filters the result of window functions, similar to how HAVING filters aggregate results. It is only supported in outer queries:

SELECT brand,
       product_name,
       billing_n_products,
       ROW_NUMBER() OVER (PARTITION BY brand ORDER BY billing_n_products DESC) AS rank_in_brand
FROM (
    SELECT dimension('product_brand')                           AS brand,
           dimension('product_name')                            AS product_name,
           metric('transactions_order_date.billing_n_products') AS billing_n_products
    FROM datamodel
    WHERE refDate BETWEEN '2025-01-01' AND '2025-06-30'
    GROUP BY brand, product_name
) AS sub
QUALIFY rank_in_brand <= 5
ORDER BY brand, rank_in_brand

7.6 UNION is not supported

NexusQL does not support the UNION, UNION ALL, INTERSECT, or EXCEPT set operators. You cannot stack the results of two SELECT statements on top of each other.

To combine results from different periods or filters, use one of the supported alternatives instead:

• For period comparisons, expose each period as its own column using overrideRefDate() (see section 4.4).
• For combining different filters on the same metric, expose each filtered value as its own column using addWhere() (see section 4.5).
• For merging data from independent queries, wrap each query in a subquery and combine them with a JOIN (see section 7.2).

---

8. Supported Functions

Beyond the NexusQL-specific functions (metric, dimension, refDateDimension), you can use many standard SQL functions in your expressions.

Math: abs, ceil, ceiling, floor, round, truncate, mod, pow, power, sqrt, exp, ln, log, log2, log10, pi, sign, greatest, least, rand

Trigonometry: sin, cos, tan, asin, acos, atan, degrees, radians

String: lower, lcase, upper, ucase, length, char_length, character_length, concat, concat_ws, substr, substring, mid, left, right, lpad, rpad, replace, reverse, repeat, position, locate, ascii, char, hex, unhex, from_base64, to_base64, md5, sha1

Date/Time: date, day, month, quarter, year, week, yearweek, dayofweek, dayofmonth, dayofyear, monthname, hour, minute, second, now, today, date_add, date_sub, date_diff, datediff, timediff, date_format, formatdatetime, from_unixtime, toMonday, toStartOfWeek, toStartOfMonth, toStartOfQuarter, toStartOfYear, toStartOfInterval, toMonth, toQuarter, toYear, toYearWeek, toYYYYMM, toYYYYMMDD, toUnixTimestamp, toWeek, toDayOfMonth, toDayOfWeek, toDayOfYear

Aggregate: sum, count, avg, min, max, any, anyLast, argMax, argMin, uniq, uniqExact, median, medianExact, quantile, quantileExact, quantiles, quantilesExact, groupArray, stddev_pop, stddev_samp, var_pop, var_samp, bit_and, bit_or, bit_xor

Conditional: if, multiIf, nullIf, ifNull, isNull, coalesce, assumeNotNull

Type conversion: cast, toString

Window-specific: row_number, rank, dense_rank, percent_rank, first_value, last_value, lagInFrame, leadInFrame, nthValue

Note: These functions follow ClickHouse syntax. Refer to the ClickHouse documentation for detailed usage.

---

9. Examples

9.1 Single metric

SELECT metric('transactions_order_date.billing_n_orders') AS total_orders
FROM datamodel
WHERE refDate BETWEEN '2023-07-01' AND '2023-07-31'

9.2 Grouping by dimension

SELECT dimension('product_brand')                           AS brand,
       metric('transactions_order_date.billing_n_products') AS product_count
FROM datamodel
WHERE refDate BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY brand
ORDER BY product_count DESC

9.3 Time-series grouping

SELECT refDateDimension(INTERVAL 1 DAY)                   AS date_day,
       metric('transactions_order_date.billing_with_vat') AS daily_billing
FROM datamodel
WHERE refDate BETWEEN '2024-02-01' AND '2024-02-28'
GROUP BY date_day
ORDER BY date_day

9.4 Filtering a specific metric with addWhere

SELECT dimension('customer_country')                                 AS customer_country,
       metric('transactions_order_date.billing_ht')                  AS billing_total,
       metric('transactions_order_date.billing_ht',
              addWhere(dimension('customer_age') BETWEEN 18 AND 25)) AS billing_young_adults
FROM datamodel
WHERE refDate BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY customer_country
ORDER BY billing_total DESC

9.5 Period-over-period comparison

SELECT metric('transactions_order_date.billing_n_orders')                                         AS current_orders,
       metric('transactions_order_date.billing_n_orders', overrideRefDate('2024-05-01', '2024-05-31')) AS previous_period_orders
FROM datamodel
WHERE refDate BETWEEN '2025-05-01' AND '2025-05-31'

9.6 Ranking and cumulative sum

SELECT rank() OVER (ORDER BY billing_n_products DESC)                  AS rank,
       product_brand,
       billing_n_products,
       sum(billing_n_products) OVER (ORDER BY billing_n_products DESC) AS billing_n_products_cum_sum
FROM (
    SELECT dimension('product_brand')                           AS product_brand,
           metric('transactions_order_date.billing_n_products') AS billing_n_products
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-10' AND '2025-01-16'
    GROUP BY product_brand
) AS t1
ORDER BY billing_n_products DESC
LIMIT 10

9.7 ABC dynamic classification

SELECT product_id,
       product_name,
       SUM(total_ca_ht) OVER ()                          AS global_ca_ht,
       total_ca_ht,
       SUM(total_ca_ht) OVER (ORDER BY total_ca_ht DESC) AS cumulative_ca_ht,
       total_ca_ht / global_ca_ht * 100                  AS ca_percentage,
       cumulative_ca_ht / global_ca_ht * 100             AS cumulative_ca_percentage,
       CASE
           WHEN cumulative_ca_percentage <= 80 THEN 'A'
           WHEN cumulative_ca_percentage <= 95 THEN 'B'
           ELSE 'C'
       END                                               AS abc_classification
FROM (
    SELECT dimension('product_id')                              AS product_id,
           dimension('product_name')                            AS product_name,
           metric('transactions_order_date.billing_product_ht') AS total_ca_ht
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-10' AND '2025-01-16'
    GROUP BY product_id, product_name
) AS st1
ORDER BY total_ca_ht DESC

9.8 Cohort analysis

SELECT t1.cohort_dimension AS cohort_dimension,
       t2.main_metric      AS main_metric,
       t1.analysis_dimension AS analysis_dimension,
       t1.analysis_metric   AS analysis_metric
FROM (
    SELECT dimension('customer_first_order_date') AS cohort_dimension,
           dateDiff('day', dimension('customer_first_order_date'), refDate) AS analysis_dimension,
           metric('transactions_order_date.billing_ht') AS analysis_metric
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-02-09'
      AND cohort_dimension BETWEEN '2024-01-01' AND '2024-01-31'
      AND analysis_dimension < 10
    GROUP BY cohort_dimension, analysis_dimension
) AS t1
LEFT JOIN (
    SELECT dimension('customer_first_order_date') AS cohort_dimension,
           count('customers_first_order_date') AS main_metric
    FROM datamodel
    WHERE refDate BETWEEN '2024-01-01' AND '2024-01-31'
    GROUP BY cohort_dimension
) AS t2 ON t1.cohort_dimension = t2.cohort_dimension
ORDER BY t1.cohort_dimension, t1.analysis_dimension

9.9 LTV (Lifetime Value)

SELECT dimension('customer_id') AS customer,
       metric('transactions_order_date.billing_ttc',
              overrideRefDate('2024-01-01', '2024-12-31'),
              addWhere(dateDiff('day', dimension('customer_first_order_date'),
                                dimension('transaction_order_date')) BETWEEN 0 AND 180)) AS ltv_180d
FROM datamodel
WHERE refDate BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY customer
ORDER BY ltv_180d DESC
LIMIT 10