Generate SQL

Generate SQL

Compiles an audience criteria definition to BigQuery SQL (GoogleSQL). A single engine serves two entry points:

• POST /api/audience-criterias/generate-sql — compiles a definition sent in the body (not yet persisted).
• GET /api/audience-criterias/:id/sql — compiles an already saved criterion, referenced by its id.

Both return the same response shape: IAudienceCriteriaSql, a discriminated union on the format field. The format (parameterized or literal) is chosen per request and governs both the shape of the query and the fields of the response.

Within a project, the SQL of every audience other than the root audience comes out intersected with the project's root audience — see Intersection with the root audience.

Auth: Required — audience-criteria:view-sql permission (both endpoints).

POST /api/audience-criterias/generate-sql

Compiles a definition sent in the body, without persisting it.

Auth: Required — audience-criteria:view-sql permission

Request Body

Root level (GenerateSqlDto): the criterion definition (CriteriaDefinitionDto) plus the format field.

Criterion Definition

It is the same tree that Create receives, without the name/description fields. It is documented here in a self-contained way.

queryBlocks[] — query block

Each element of queryBlocks (QueryBlockDto) is a complete SQL statement:

ColumnRefDto — column reference

Used throughout the tree (select, filters, group by, joins, aggregates, time window):

Every column is always qualified with its table to avoid ambiguity.

from — data source (FromDto)

Each JoinDto:

Each JoinPredicateDto is a pair of columns: { left: ColumnRefDto, right: ColumnRefDto }.

select — projection (SelectDto)

The items are discriminated by kind:

• Column — kind: "column":

  Field	Type	Required	Description
  kind	"column"	Yes	Discriminator.
  column	ColumnRefDto	Yes	Projected column.
  alias	string	No	Must match ^[A-Za-z_][A-Za-z0-9_]*$.

• Aggregate — kind: "aggregate":

  Field	Type	Required	Description
  kind	"aggregate"	Yes	Discriminator.
  aggregate	AggregateDto	Yes	Aggregation expression.
  alias	string	Yes	Here the alias is required. Same identifier regex.

AggregateDto:

operand — what is aggregated — is discriminated by kind:

ArithmeticOperatorEnum: ADD | SUBTRACT | MULTIPLY | SAFE_DIVIDE (a division that yields NULL when the denominator is zero instead of failing the query). The columns of an arithmetic expression must be numeric (INT64, NUMERIC, BIGNUMERIC, FLOAT64), and expressions nest up to 3 levels of arithmetic, counting the outermost one.

where — predicate tree (WhereDto)

The where is a recursive tree. The root node is always a group, and its leaves come in two kinds:

Group — kind: "group"

Column leaf — kind: "predicate"

Compares a scalar column qualified by its table.

{
  "kind": "predicate",
  "column": { "table": "user_properties", "column": "country" },
  "comparisonOperator": "IN",
  "value": ["CL", "MX"]
}

Record leaf — kind: "recordPredicate"

Filters by a RECORD REPEATED column (an array of structs, such as attributes). It means at least one element of the array satisfies the sub-filter.

{
  "kind": "recordPredicate",
  "column": { "table": "event_transactions", "column": "attributes" },
  "subWhere": {
    "kind": "group",
    "logicalOperator": "AND",
    "children": [
      { "kind": "predicate", "field": "key", "comparisonOperator": "EQUALS", "value": "canal" },
      { "kind": "predicate", "field": "value", "comparisonOperator": "EQUALS", "value": "web" }
    ]
  }
}

Which columns accept a record filter is stated by the catalog: the ones that come with kind: "record".

Relative sub-tree

The subWhere is an AND/OR group whose leaves are resolved against the sub-fields the record declares.

There is a single difference with the top-level tree, and it is the most common source of errors:

Leaves allowed inside a relative sub-tree:

• Scalar leaf — { "kind": "predicate", "field": string, "comparisonOperator": …, "value"?: … }. The field must be a scalar sub-field of the record.
• Nested record — { "kind": "recordPredicate", "field": string, "subWhere": … }. A record inside another record; it nests with no declared limit, as long as the catalog declares each level.
• Group — the same group shape, to combine the previous leaves with AND/OR.

value shape by comparisonOperator

ScalarValue = string | number | boolean. In addition, each operator must be compatible with the column's type (see validation rules).

groupBy — grouping columns

Optional list of ColumnRefDto[]. When the block aggregates (declares groupBy or has an aggregate in the select), the groupBy must match exactly the non-aggregated columns of the select (see validation rules).

having — filter on aggregates (HavingDto)

Same recursive structure as where (nested AND/OR groups and the same value rule per operator), but the leaf operates on an aggregate, not on a column:

• Predicate (leaf) — { kind: "predicate", aggregate: AggregateDto, comparisonOperator: ComparisonOperatorEnum, value?: PredicateValue }.
• Group — identical to the one in where.

timeWindow — time window (TimeWindowDto)

Discriminated union on timeOperator. Narrows the block to a time period over a TIMESTAMP column from the catalog (reference). It is a sibling field of where, not a node of the filter tree.

TimeUnitEnum: DAYS | WEEKS | MONTHS | YEARS.

Business semantics of each operator:

• ALL_TIME — no window; considers the entire history.
• IN_THE_LAST — rolling window relative to "now": the last value unit (e.g. last 30 DAYS). Recomputed on each run.
• IN_THE_FIRST — window relative to the origin of the user's lifecycle (their signup date): the first value unit of each user's life. Anchored per user, not to a fixed date.
• SINCE — from the civil date from (inclusive) onward.
• BETWEEN — between from and to, both civil dates inclusive of their full day.

composedWith[] — composition with other audiences

Each element (CreateCompositionDto) combines this definition with another audience via a set operation:

Example

Definition of an audience of users with a transaction within their first 30 days since signup (IN_THE_FIRST). By omitting format, the response is parameterized (default).

curl -X POST https://api.reten.ai/api/audience-criterias/generate-sql \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "x-project-id: <project-id>" \
  -H "Content-Type: application/json" \
  -d '{
    "queryBlocks": [
      {
        "order": 0,
        "from": { "table": "event_transactions" },
        "select": {
          "distinct": true,
          "items": [
            {
              "kind": "column",
              "column": { "table": "event_transactions", "column": "user_id" }
            }
          ]
        },
        "timeWindow": {
          "timeOperator": "IN_THE_FIRST",
          "reference": { "table": "event_transactions", "column": "event_date" },
          "unit": "DAYS",
          "value": 30
        },
        "where": { "kind": "group", "logicalOperator": "AND", "children": [] }
      }
    ]
  }'

import axios from 'axios';

const body = {
  queryBlocks: [
    {
      order: 0,
      from: { table: 'event_transactions' },
      select: {
        distinct: true,
        items: [
          {
            kind: 'column',
            column: { table: 'event_transactions', column: 'user_id' },
          },
        ],
      },
      timeWindow: {
        timeOperator: 'IN_THE_FIRST',
        reference: { table: 'event_transactions', column: 'event_date' },
        unit: 'DAYS',
        value: 30,
      },
      where: { kind: 'group', logicalOperator: 'AND', children: [] },
    },
  ],
  // format: 'literal',  // optional; defaults to 'parameterized'
};

const response = await axios.post(
  'https://api.reten.ai/api/audience-criterias/generate-sql',
  body,
  { headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-id>' } },
);
const sql = response.data; // IAudienceCriteriaSql

The response is documented in IAudienceCriteriaSql, with an example of each format.

GET /api/audience-criterias/:id/sql

Compiles an already saved criterion, referenced by its id. The definition does not travel in the body: it is taken from the criterion persisted in the active project.

Auth: Required — audience-criteria:view-sql permission

Status 200 OK (it is a GET).

Path Parameters

Query Parameters

GetSqlDto:

Example

curl "https://api.reten.ai/api/audience-criterias/f47ac10b-58cc-4372-a567-0e02b2c3d479/sql?format=literal" \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "x-project-id: <project-id>"

import axios from 'axios';

const id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
const response = await axios.get(
  `https://api.reten.ai/api/audience-criterias/${id}/sql`,
  {
    params: { format: 'literal' }, // optional; defaults to 'parameterized'
    headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-id>' },
  },
);
const sql = response.data; // IAudienceCriteriaSql

Output format — format

The format field decides how the SQL values are emitted and which fields the response carries. It is optional; if omitted, the effective value is parameterized.

• In generate-sql (POST) the format goes in the body.
• In :id/sql (GET) the format goes as a query param.

Only the strings "parameterized" and "literal" are accepted; any other value produces a 400.

Response — IAudienceCriteriaSql

It is a discriminated union on format. Both variants share a base and differ in the binding fields.

Base (common to both formats):

• format: "parameterized" — IParameterizedAudienceCriteriaSql:

  Field	Type	Description
  format	"parameterized"	Discriminator.
  query	string	CTE chain with @pN placeholders.
  finalCteName	string	Name of the final CTE.
  subjectColumn	string	Identity column exposed by the final CTE.
  granularity	"table_row" | "record_element"	What one row of the final CTE is (in an audience, always table_row).
  params	Record<string, ScalarValue>	Binding of each @pN → its value. ScalarValue = string | number | boolean.
  types	Record<string, string>	BigQuery parameter type of each @pN (e.g. STRING, INT64, TIMESTAMP, BOOL, FLOAT64, NUMERIC, BIGNUMERIC).

• format: "literal" — ILiteralAudienceCriteriaSql:

  Field	Type	Description
  format	"literal"	Discriminator.
  query	string	CTE chain with the values already inlined as literals.
  finalCteName	string	Name of the final CTE.
  subjectColumn	string	Identity column exposed by the final CTE.
  granularity	"table_row" | "record_element"	What one row of the final CTE is (in an audience, always table_row).

Example — parameterized (default)

201 response to the example POST above (without format):

{
  "format": "parameterized",
  "query": "op_5e2a91c07b3d_0 AS (\nSELECT DISTINCT `event_transactions`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions\nINNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`\nWHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL @p0 DAY))\n),\nop_9a8b7c6d5e4f_0 AS (\nSELECT DISTINCT `user_properties`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties\nWHERE `user_properties`.`is_active` = @p1\n),\nCTE_AUDIENCE_USERS AS (\nSELECT * FROM op_5e2a91c07b3d_0\nINTERSECT DISTINCT\nSELECT * FROM op_9a8b7c6d5e4f_0\n)",
  "finalCteName": "CTE_AUDIENCE_USERS",
  "subjectColumn": "user_id",
  "granularity": "table_row",
  "params": { "p0": 30, "p1": true },
  "types": { "p0": "INT64", "p1": "BOOL" }
}

The query is a single string with \n line breaks — a CTE chain without the leading WITH and without a trailing SELECT. Formatted for readability:

op_5e2a91c07b3d_0 AS (
SELECT DISTINCT `event_transactions`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions
INNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`
WHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL @p0 DAY))
),
op_9a8b7c6d5e4f_0 AS (
SELECT DISTINCT `user_properties`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties
WHERE `user_properties`.`is_active` = @p1
),
CTE_AUDIENCE_USERS AS (
SELECT * FROM op_5e2a91c07b3d_0
INTERSECT DISTINCT
SELECT * FROM op_9a8b7c6d5e4f_0
)

Each @pN is bound with params[pN] (its value) and types[pN] (its BigQuery type); without that binding the query is not executable. p0 belongs to the audience and p1 to the project's root audience, which here keeps its default definition (see Intersection with the root audience). The INNER JOIN to user_properties is not in the request: IN_THE_FIRST measures each user's first days from their signup date, so the API adds it.

Example — literal

The same definition with format: "literal" (in the by-id variant, ?format=literal):

{
  "format": "literal",
  "query": "op_5e2a91c07b3d_0 AS (\nSELECT DISTINCT `event_transactions`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions\nINNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`\nWHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL 30 DAY))\n),\nop_9a8b7c6d5e4f_0 AS (\nSELECT DISTINCT `user_properties`.`user_id`\nFROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties\nWHERE `user_properties`.`is_active` = TRUE\n),\nCTE_AUDIENCE_USERS AS (\nSELECT * FROM op_5e2a91c07b3d_0\nINTERSECT DISTINCT\nSELECT * FROM op_9a8b7c6d5e4f_0\n)",
  "finalCteName": "CTE_AUDIENCE_USERS",
  "subjectColumn": "user_id",
  "granularity": "table_row"
}

Formatted for readability:

op_5e2a91c07b3d_0 AS (
SELECT DISTINCT `event_transactions`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions
INNER JOIN `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties ON `event_transactions`.`user_id` = `user_properties`.`user_id`
WHERE `event_transactions`.`event_date` BETWEEN `user_properties`.`sign_up_date` AND TIMESTAMP(DATETIME_ADD(DATETIME(`user_properties`.`sign_up_date`), INTERVAL 30 DAY))
),
op_9a8b7c6d5e4f_0 AS (
SELECT DISTINCT `user_properties`.`user_id`
FROM `reten-prod.tenant_a1b2c3d4.user_properties` AS user_properties
WHERE `user_properties`.`is_active` = TRUE
),
CTE_AUDIENCE_USERS AS (
SELECT * FROM op_5e2a91c07b3d_0
INTERSECT DISTINCT
SELECT * FROM op_9a8b7c6d5e4f_0
)

The values are inlined as safe literals (here 30 and TRUE; strings go escaped, in single quotes); there are no params or types.

Intersection with the root audience

Every project has a root audience, and every other audience of the project is evaluated within it. On both endpoints, the SQL of any audience that is not the root audience comes out intersected with the project's root audience:

• The audience and the root audience keep their own CTEs, and the final CTE intersects the last CTE of each: CTE_AUDIENCE_USERS AS (SELECT * FROM <audience CTE> INTERSECT DISTINCT SELECT * FROM <root audience CTE>).
• In the parameterized format, the audience's @pN parameters come first and the root audience's parameters follow.
• There is no intersection when the compiled audience is the root audience itself — GET /:id/sql with the root audience's id, or a draft whose criteriaId is the root audience.
• The intersection is positional (SELECT * on both sides): the audience must project the same columns as the root audience, in the same order and with compatible types — with the root audience's default definition, only user_id. Otherwise, the SQL fails when it runs.

The intermediate CTEs carry a prefix of each audience's id in their names (for a draft sent without criteriaId, that prefix changes on every request), so always reference the result through finalCteName.

For example, an audience of users with a COMPLETED transaction, in a project whose root audience was edited to country = 'CL' (literal format, formatted for readability):

op_f47ac10b58cc_0 AS (
SELECT DISTINCT `event_transactions`.`user_id`
FROM `<gcp-project>.<dataset>.event_transactions` AS event_transactions
WHERE `event_transactions`.`status` = 'COMPLETED'
),
op_9a8b7c6d5e4f_0 AS (
SELECT DISTINCT `user_properties`.`user_id`
FROM `<gcp-project>.<dataset>.user_properties` AS user_properties
WHERE `user_properties`.`country` = 'CL'
),
CTE_AUDIENCE_USERS AS (
SELECT * FROM op_f47ac10b58cc_0
INTERSECT DISTINCT
SELECT * FROM op_9a8b7c6d5e4f_0
)

Validation Rules

Before compiling, the definition goes through the same two layers of validation as Create, and both return 400 Bad Request:

1. DTO validation (structure, types, enums, required fields, valid format). message is an array of strings and unknown properties are rejected (including name/description).
2. Business validation (the definition must generate valid SQL coherent against the catalog). message is a single string with the violated rule.

Error Responses