NexusQL Language Reference

This document provides an overview of the NexusQL language — a custom SQL-like syntax for querying Nexus, the OLAP-like database provided by Biron.

It focuses on the distinctive features that differ from standard SQL. For standard SQL constructs (e.g., CASE expressions, ORDER BY, etc.), 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.

---

2. Key Differences from Standard SQL

1. Mandatory FROM Clause

• You must always query from datamodel (i.e., FROM datamodel) when retrieving metrics and dimensions.
• If you need to perform window functions on the result of a subquery, or combine multiple subqueries, you can use an outer query that references an inner query. However, the inner query typically uses FROM datamodel.

2. Metrics / Aggregate functions

• Standard aggregate functions like SUM, COUNT, or AVG are replaced by a single function called metric().
• The first argument of metric is always a string composed of <viewCode>.<metricCode>.
• Example: metric('transactions_order_date.billing_with_vat') retrieves the metric named billing_with_vat from the view transactions_order_date.
• Additional arguments can specify overrides or filters (e.g., overrideRefDate, addWhere), discussed later.
• At least one metric must be selected.

3. Dimensions / Slicers

• To segment or group by a dimension, use the function dimension('dimensionCode'), use the dimension('dimension_code') function.
• For example, dimension('customer_age') references a dimension called customer_age.
• For time-based grouping, use refDateDimension(INTERVAL 1 <period>).
• The <period> can be DAY, WEEK, MONTH, QUARTER, or YEAR.
• Example: refDateDimension(INTERVAL 1 MONTH) groups data by month.
• If you want to group results by a dimension, you must include that dimension in the GROUP BY clause (either directly or by using the same alias assigned in SELECT).

4. Filtering

• You must filter on refDate in the WHERE clause using a BETWEEN operation for lower and upper boundaries.
• Example: WHERE refDate BETWEEN '2023-01-01' AND '2023-01-31'.
• Additional boolean conditions can filter dimensions, e.g., dimension('customer_age') > 30.

5. Grouping

• All selected expressions (except metric ones) must appear in the GROUP BY clause.
• In practice, dimensions or any expression aliased in the SELECT must be grouped on.

---

3. Basic Query Skeleton

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 other dimension filters ...]
        [GROUP BY [ date_alias, dim_alias, ...]]
    [
    ORDER BY [ date_alias, dim_alias, metric_alias, ... ]]
    [
    LIMIT <n,m>]

Note: All non-metric columns or expressions in SELECT must appear in GROUP BY. The WHERE clause must include a refDate filter.

---

4. Functions and Special Constructs

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

• Description: Retrieves a predefined metric.
• Mandatory Argument: A string indicating <viewCode>.<metricCode>.
• Optional Arguments:
  • overrideRefDate('start', 'end'): Temporarily change the date range for this metric (could be used only once per metric).
  • addWhere(<expression>): Apply additional filters only for this metric, the expression is similar to the one that you could use in WHERE clause.

Example:

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

More examples for overrideRefDate and addWhere bellow.

3.2 dimension('dimension_code')

• Description: References a dimension by its code.
• Usage: In SELECT, WHERE, or GROUP BY to specify dimension-based grouping or filtering.

Example:

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

3.3 refDateDimension(INTERVAL 1 <period>)

• Description: Generates time segments based on a specified interval, e.g., day, week, month.
• Allowed Periods: DAY, WEEK, MONTH, QUARTER, YEAR.

Example:

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

3.4 overrideRefDate('start', 'end')

• Description: When passed as an additional argument to metric, it replaces the default date range for that metric.

Example:

    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'

3.5 addWhere(<expr>)

• Description: Applies an extra condition that affects only the associated metric calculation, not the entire query.

Example:

    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. Simple Examples

5.1 Selecting a 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'

5.2 Grouping by a 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

5.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

5.4 Using addWhere to Filter a Specific Metric

    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

5.5 Overriding the Date Range for One Metric

    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'

---

6. Subqueries, Joins, and QUALIFY

6.1. Subqueries

You can place a NexusQL query (which selects from datamodel) inside another query to perform post-processing. In such cases, standard SQL rules apply to the outer query, so you could use standard aggregation functions, HAVING or JOIN clauses, etc

Though most analyses require only one FROM datamodel, you can use standard SQL join syntax between subqueries to perform more computations.

6.2. Window Functions & QUALIFY clause

Window functions (e.g., ROW_NUMBER() OVER(...), RANK() OVER(...), etc.) and the QUALIFY clause are only supported outside of FROM datamodel usage.

Typically, you select your data from datamodel in a subquery, then the outer query can apply window functions and/or a QUALIFY filter.

Examples :

SELECT...,
    RANK() OVER(...)
FROM some_subquery

or

    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. Additional Notes

• Performance: NexusQL leverages a specialized engine behind datamodel. Ensure your WHERE clause with refDate BETWEEN ... is well-defined to utilize optimizations.
• Aliasing: In WHERE, GROUP BY, ORDER BY, you can reference dimensions and refDateDimension by their aliases or by reusing the same function calls.

---

8. Summary

NexusQL extends SQL with specialized functions for OLAP-style metrics and dimensions while enforcing:

1. A fixed FROM datamodel.
2. A mandatory refDate range in WHERE.
3. Grouping all selected dimensions.
4. Specialized functions (metric, dimension, refDateDimension, overrideRefDate, addWhere) tailored to business analytics needs.

For more advanced operations—window functions, QUALIFY, etc.—wrap your primary NexusQL query in an outer query.

Feel free to explore the provided examples to get started quickly, and refer to standard SQL resources for common syntax and operations not outlined here.

---

9. More advanced queries

9.1 ranking & 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.2 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.3 Cohort

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.4 LTV

SELECT dimension('customer_id')                                                                                                         AS d0,
       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 m0
FROM datamodel
WHERE refDate BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY d0
ORDER BY m0 DESC
LIMIT 10