Cas d’usage

Analyse de cohortes

CA Induit

[WIP] Produits Achetés avec

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.

<aside> 💡 You can find the IDs for the different objects in the data dictionary within the ID NexusQL field.

</aside>

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