Query structure

Basic query structure

WITH
    -- Input definitions
    INPUT_FEATURE := INPUT(BIGINT),
    -- External data
    -- Derived features (composition, enrichment, transformation)
SELECT
    feature1,
    feature2...
FOR
    INPUT_FEATURE := BIND()
;

A FeatureQL query basic query is made of three main parts:

• The WITH clause defines features that can be used in the query, in addition to the features already defined in the registry. These features won't necessarily be evaluated, depending on the features returned in the SELECT clause.
• The SELECT clause defines the features that will be evaluated by the query. They must all depend on the same inputs, which values are defined in the FOR clause.
• The FOR clause defines the values that will be bound to the inputs for evaluation. They must all be of the same type as the inputs defined in the WITH clause.

Query with filtering and ordering

WITH
    -- Input definitions
    INPUT_FEATURE := INPUT(BIGINT),
    -- External data
    -- Derived features (composition, enrichment, transformation)
SELECT
    feature1,
    feature2...
FOR
    INPUT_FEATURE := BIND()
WHERE
    condition
ORDER BY
    feature1, feature2...
OFFSET m
LIMIT n;

You can add the following optional clauses to better control the query results:

• The WHERE clause filters results by feature values.
• The ORDER BY clause sorts results by feature values.
• The LIMIT clause restricts the number of returned rows to n.
• The OFFSET clause skips m rows for pagination.

Notes: Grouping and unnesting are supported for analytics queries. See Grouping and unnesting for more information.

Prototyping

Feature definitions are evaluated lazily. It means that if you don't use a feature in the SELECT clause, it will not be evaluated. It is convenient for prototyping, as what you put in the SELECT governs what is evaluated.

Using the exact same query with customer outputs, you can see that the query is evaluated for the customers 100, 101 and 102.

Using the exact same query with order outputs, you can see that the query is evaluated for the orders 200, 201, 202 and 203.

Once you are happy with your query, we recommend you to clean the definitions to keep only the features needed for clarity.

Note: Be careful, if you use the exact same query with customer outputs and order outputs, it will perform a cross-join between the customers and the orders, which is likely not what you want.