> This page is for Kumo Engine.

> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.nvidia.com/sdgm/llms.txt.

# Predictive Queries

> Turn a business question into a predictive query: say what to predict, for which entities, and over what time window, in Predictive Query Language (PQL)

Quick links

[**Get Started: Write a Query**Turn a business question into a predictive query](#build-your-first-predictive-query)

[**Examples**Supported predictive task types and example queries](#supported-task-types)

[**PQL Syntax**Supported clauses, aggregations, windows, filters](#supported-clauses)

## What Is a Predictive Query?

A **Predictive Query** is a declarative syntax that defines a predictive modeling task in Kumo.
It specifies the target variable to predict and the data context for training.

Kumo uses **Predictive Query Language (PQL)**, a SQL-like syntax, to automate the ML pipeline, including feature engineering, training table generation, and model training.

Kumo does three things with your query:

1. **Build labels.** Kumo picks many past points in time. At each one, it computes the true answer from your history (*e.g., whether each customer actually ordered in the following 30 days*). Each answer becomes a training label, and together they form your [training table](/sdgm/kumo-engine/user-code-guide/train-a-model/training-tables). How labels are built depends on the query type: a [temporal query](#every-pql-is-static-or-temporal) computes them at many past anchor times, while a [static query](#every-pql-is-static-or-temporal) reads them from existing column values.
2. **Train.** Kumo trains a model that learns to predict those labels from everything connected to the entity in your graph: the customer's profile, past orders, the products in those orders, and so on. You do not write features.
3. **Predict.** Kumo applies the same query at a point in time you choose, usually now, and scores each entity.

Because the same query drives labels, training, and scoring, changing the question means changing one statement, not rebuilding a pipeline.

## Build Your First Predictive Query

Suppose a retail team asks: "Which of our recently active customers will order again in the next 30 days?"
This translates to:

**`PQL`**

```sql PQL
PREDICT COUNT(orders.*, 0, 30, days) > 0
FOR EACH customers.customer_id
WHERE COUNT(orders.*, -90, 0, days) > 0
```

Each part of the question becomes one part of the query:

![The business question "Which of our recently active customers will order again in the next 30 days?" split into four color-coded parts. "Recently active" is the eligible entities and becomes WHERE COUNT(orders., -90, 0, days) > 0. "Customers" is the entity and becomes FOR EACH customers.customer\_id. "Order again" is the outcome and becomes COUNT(orders., ...) > 0 after PREDICT. "Next 30 days" is the window and becomes the arguments 0, 30, days.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/102c3d0a5173535dd3343ef4cba4f5624356f106b94b1fb5b9875f75eea36580/_dot_dot_/images/diagrams/pql-business-question.svg)

There are two ways to get from your own question to a query:

#### Write It Yourself

Answer four questions about your problem, and write each answer as a clause:

| Question                                                 | Clause                                          |
| -------------------------------------------------------- | ----------------------------------------------- |
| **Entity:** which entity gets a prediction?              | `FOR EACH <table>.<primary_key>`                |
| **Outcome:** what do you want to predict?                | `PREDICT <column, aggregation, or condition>`   |
| **Window:** over what period?                            | `<start>, <end>, <unit>` inside the aggregation |
| **Eligible:** which entities should be scored? Optional. | `WHERE <condition>` after `FOR EACH`            |

To check the query against your graph before training, see [Use PQL in Python](#use-pql-in-python).

#### Let an Agent Write It

Install the `kumo-data-science` skill by following [Get Started with Agents](/sdgm/kumo-engine/get-started-with-kumo-engine/agents).
Then ask your agent the business question in plain language, and say where your data lives:

```text
Train a Kumo model on the retail tables in ./retail-data.
For each customer who ordered in the past 90 days, predict
whether they place at least one order in the next 30 days.
```

## Use PQL in Python

To use a query in practice, create a `PredictiveQuery` from a `kumo.engine.Graph` and the PQL string.

```python
from kumo.engine import PredictiveQuery

query = PredictiveQuery(
    graph,
    """
    PREDICT COUNT(orders.*, 0, 30, days) > 0
    FOR EACH customers.customer_id
    WHERE COUNT(orders.*, -90, 0, days) > 0
    """,
)
response = query.validate()   # logs and returns warnings; raises if the query is invalid
print(query.get_task_type())  # binary_classification
```

Next, turn the query into labeled rows with `query.generate_training_table(snapshot)` (see [Training Tables](/sdgm/kumo-engine/user-code-guide/train-a-model/training-tables)), and score entities with `query.generate_prediction_table(snapshot, prediction_time=...)` (see [Prediction Guide](/sdgm/kumo-engine/user-code-guide/making-predictions)).

## PQL Deep Dive

The rest of this page is for when you need more than a first query.
It covers the task types PQL supports, the full syntax, and the details that decide whether a model learns the right thing: static versus temporal queries, anchor times, and data leakage.

* [The Example Data](#the-example-data): the three retail tables every example uses.
* [Query Structure](#query-structure): the shape of every query and the forms a target can take.
* [Every PQL Is Static or Temporal](#every-pql-is-static-or-temporal): how the target decides what Kumo trains on and scores.
* [Supported Task Types](#supported-task-types): the business question, query, and output for each task type.
* [Appendix](#appendix): anchor time, data leakage, and the full PQL reference.

## The Example Data

Every query on this page uses the same three tables from a retail store.

![The example schema. The orders table has foreign keys to customers and products. Customers and orders have time columns; products does not. The type next to each column is its semantic type.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/ca58624808a7c165eee571297700e240d436955b19e39bf281de308fd8710221/_dot_dot_/images/diagrams/pql-example-schema.svg)

## Query Structure

Every predictive query has the same shape.
Square brackets mark optional parts; angle brackets are placeholders.

**`PQL general form`**

```sql PQL general form
PREDICT <target> [RANK TOP <k> | CLASSIFY [TOP <k>] | FORECAST <n> TIMEFRAMES]
FOR EACH <table>.<primary_key> [WHERE <entity filter>]
[ASSUMING <condition>]
```

The query below uses most of these parts.
It predicts how much each recently active customer will spend by card in the next 30 days.

![An annotated predictive query. The target is the sum of orders.amount for card payments over the next 30 days, the entity is customers.customer\_id, and the entity filter keeps customers with at least one order in the last 90 days.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/421f0055838f51308b239976e6cd1f4ae72de133a0e5051cf5d3fa912b20d1a2/_dot_dot_/images/diagrams/pql-query-anatomy.svg)

The target after `PREDICT` takes one of three forms:

* **A column**, such as `PREDICT customers.loyalty_tier`. Use this to fill in values that some entities have and others do not.
* **An aggregation over a time window**, such as `PREDICT SUM(orders.amount, 0, 30, days)`. Use this to predict what will happen.
* **A condition** built from either, such as `PREDICT customers.loyalty_tier = 'gold'` or `PREDICT COUNT(orders.*, 0, 30, days) > 0`. A condition always produces a yes or no answer.

For every clause and the rules behind it, such as which columns a target can use and where a time window may point, see [Supported Clauses](#supported-clauses) in the [Appendix](#appendix).

## Every PQL Is Static or Temporal

The rule is simple: **a query is temporal when its target has a time window, and static otherwise.**
The choice decides how Kumo builds training rows and what it scores.

**Static: predict a value that is missing.**
Orders that already have a fraud label train the model.
Orders without one are scored.

**`PQL`**

```sql PQL
PREDICT orders.is_fraud
FOR EACH orders.order_id
```

**Temporal: predict what happens next.**
For each recently active customer, will they stop ordering in the next 30 days?

**`PQL`**

```sql PQL
PREDICT COUNT(orders.*, 0, 30, days) = 0
FOR EACH customers.customer_id
WHERE COUNT(orders.*, -90, 0, days) > 0
```

| Aspect              | Static query                                                        | Temporal query                                                            |
| ------------------- | ------------------------------------------------------------------- | ------------------------------------------------------------------------- |
| **Target**          | A column, or a condition on columns                                 | An aggregation over a future time window                                  |
| **Question**        | What is true of this row?                                           | What will happen to this entity next?                                     |
| **Training rows**   | One per entity whose target has a value                             | One per eligible entity at each of many past [anchor times](#anchor-time) |
| **Split**           | Random 80/10/10, or by time when the entity table has a time column | By anchor time: validation and test come after training                   |
| **Scored rows**     | Entities whose target is missing, or rows you supply                | Entities that pass the entity filter at the prediction time               |
| **Prediction time** | Not accepted                                                        | Optional; defaults to the latest data                                     |

**Which should you use?** Ask when the answer becomes known.
If it is already recorded on the row, or recorded when the row is created (a fraud label, a customer's tier), use a static query.
If it depends on events after the moment you score (future orders, churn, demand), use a temporal query.

> **Note**
>
> **Rules for static queries.** A static query cannot use a time window anywhere, including in its entity filter.
> It cannot use `ASSUMING` or `FORECAST`, and it does not accept a prediction time.
> A target cannot mix a static part and a temporal part.

A temporal query is only as trustworthy as the timestamps behind it; see [Avoid Data Leakage](#avoid-data-leakage).

## Supported Task Types

The **task type** is the kind of machine learning problem Kumo trains for.
You do not set it.
Kumo infers it from three things: the form of the target, the semantic type of the value it produces, and the `RANK`, `CLASSIFY`, or `FORECAST` modifier.
Call [`get_task_type()`](#use-pql-in-python) to confirm it before training.

![Decision flow for task types. A true or false target gives binary classification. Otherwise a list of foreign keys or classes gives multilabel classification with CLASSIFY, multilabel ranking with RANK TOP k over classes, or link prediction with RANK TOP k over foreign keys. Otherwise a single class gives multiclass classification, and a numerical target gives forecasting with FORECAST or regression without.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/51c4b2e288e95f58a3a5560286f2c35e377a104f62a943a6c5e9e306cdeadf3d/_dot_dot_/images/diagrams/pql-task-type-flow.svg)

Ask the questions in order and stop at the first match.

| Task type                                               | Typical PQL shape                                            | Main output columns        |
| ------------------------------------------------------- | ------------------------------------------------------------ | -------------------------- |
| [Binary classification](#binary-classification)         | `... > 0`, `... = 'gold'`, boolean column                    | `TARGET_PRED`, `True_PROB` |
| [Multiclass classification](#multiclass-classification) | Categorical column, `FIRST` or `LAST` of one                 | `CLASS`, `SCORE`           |
| [Multilabel classification](#multilabel-classification) | List target with `CLASSIFY`                                  | `CLASS`, `SCORE`           |
| [Multilabel ranking](#multilabel-ranking)               | List target of a column that is not a key, with `RANK TOP k` | `CLASS`, `SCORE`           |
| [Regression](#regression)                               | Numerical column or aggregation                              | `TARGET_PRED`              |
| [Forecasting](#forecasting)                             | Numerical aggregation with `FORECAST n TIMEFRAMES`           | `TIMESTAMP`, `TARGET_PRED` |
| [Temporal link prediction](#temporal-link-prediction)   | `LIST_DISTINCT(fk, window) RANK TOP k`                       | `CLASS`, `SCORE`           |
| [Static link prediction](#static-link-prediction)       | `LIST_DISTINCT(fk) RANK TOP k`                               | `CLASS`, `SCORE`           |

Each task type below starts with the business question it answers, then the query, then sample output.
For complete queries on other datasets, see [Predictive Query Examples](/sdgm/kumo-engine/get-started-with-kumo-engine/workflows-and-solutions/predictive-query-examples).

### Binary Classification

**Churn.** Will an active customer stop ordering in the next 30 days?

**`PQL`**

```sql PQL
PREDICT COUNT(orders.*, 0, 30, days) = 0
FOR EACH customers.customer_id
WHERE COUNT(orders.*, -90, 0, days) > 0
```

| ENTITY | TARGET_PRED | False_PROB | True_PROB |
| ------ | ----------- | ---------- | --------- |
| 1042   | true        | 0.18       | 0.82      |
| 1043   | false       | 0.91       | 0.09      |

`True_PROB` is the probability that the condition is true.
Any condition is binary classification, so `> 0` (will order) works the same way as `= 0` (will not order).

### Multiclass Classification

**Next payment method.** Which payment method will the customer use first in the next 30 days?

**`PQL`**

```sql PQL
PREDICT FIRST(orders.payment_method, 0, 30, days)
FOR EACH customers.customer_id
```

| ENTITY | PREDICTED | CLASS     | SCORE |
| ------ | --------- | --------- | ----- |
| 1042   | true      | card      | 0.71  |
| 1042   | false     | gift_card | 0.19  |
| 1042   | false     | paypal    | 0.10  |

One row per returned class for each entity; `PREDICTED` marks the top class.

### Multilabel Classification

**Payment methods in use.** Which payment methods will each customer use in the next 30 days?

**`PQL`**

```sql PQL
PREDICT LIST_DISTINCT(orders.payment_method, 0, 30, days) CLASSIFY
FOR EACH customers.customer_id
```

| ENTITY | PREDICTED | CLASS     | SCORE |
| ------ | --------- | --------- | ----- |
| 1042   | true      | card      | 0.88  |
| 1042   | true      | paypal    | 0.57  |
| 1042   | false     | gift_card | 0.12  |

Each label is predicted independently, so several can be `true`.
Static version: `PREDICT products.tags CLASSIFY FOR EACH products.product_id` fills in missing tags.

### Multilabel Ranking

**Payment methods to offer first.** Which two payment methods is each customer most likely to use next month?

**`PQL`**

```sql PQL
PREDICT LIST_DISTINCT(orders.payment_method, 0, 30, days) RANK TOP 2
FOR EACH customers.customer_id
```

| ENTITY | CLASS  | SCORE |
| ------ | ------ | ----- |
| 1042   | card   | 0.88  |
| 1042   | paypal | 0.57  |

### Regression

**Next month's spend.** How much will each customer spend in the next 30 days?

**`PQL`**

```sql PQL
PREDICT SUM(orders.amount, 0, 30, days)
FOR EACH customers.customer_id
```

| ENTITY | TARGET_PRED |
| ------ | ----------- |
| 1042   | 212.40      |
| 1043   | 0.00        |

Static version: `PREDICT products.list_price FOR EACH products.product_id`.

### Forecasting

**Weekly revenue forecast.** How much revenue will each product bring in during each of the next four weeks?

**`PQL`**

```sql PQL
PREDICT SUM(orders.amount, 0, 7, days) FORECAST 4 TIMEFRAMES
FOR EACH products.product_id
```

| ENTITY | TIMESTAMP  | TARGET_PRED |
| ------ | ---------- | ----------- |
| 77     | 2025-10-01 | 1380.2      |
| 77     | 2025-10-08 | 1295.7      |
| 77     | ...        | ...         |

Each product gets four weekly figures; `TIMESTAMP` is the start of each window.
See [Generate Forecast Inputs](/sdgm/kumo-engine/user-code-guide/train-a-model/training-tables#generate-forecast-inputs).

### Temporal Link Prediction

**Products to recommend.** Which 10 products is each customer most likely to buy in the next 30 days?

**`PQL`**

```sql PQL
PREDICT LIST_DISTINCT(orders.product_id, 0, 30, days) RANK TOP 10
FOR EACH customers.customer_id
```

| customer_id | CLASS | SCORE |
| ----------- | ----- | ----- |
| 1042        | 77    | 0.93  |
| 1042        | 512   | 0.88  |
| 1042        | ...   | ...   |

`CLASS` holds the recommended item's key (a `product_id`).

### Static Link Prediction

**Recommendations without timestamps.** Which 10 products is each customer most likely to buy, when your data does not record when purchases happened?

Static link prediction is the only task whose target aggregation has no time window, and it only works when **no table in the graph has a time column**.
For this example, imagine a graph with `customers`, `products`, and a `purchases` table that links them, none of which have time columns:

**`PQL`**

```sql PQL
PREDICT LIST_DISTINCT(purchases.product_id) RANK TOP 10
FOR EACH customers.customer_id
```

The output has the same columns as temporal link prediction.

> **Tip**
>
> **How many results come back.** For multiclass, multilabel, and link prediction, choose how many classes or items to return per entity with `num_classes_to_return` at prediction time.
> It defaults to `k` from `TOP k`, and link prediction requires it (at most 500) when you export predictions.
> See [Task-Specific Output Options](/sdgm/kumo-engine/user-code-guide/advanced-prediction-configurations#task-specific-output-options).

## Appendix

Reference material for the deep dive:

* [Anchor Time](#anchor-time): how Kumo separates known data from the future, and how to avoid data leakage.
* [Supported Clauses](#supported-clauses): every clause in a query, with the rules for targets, entities, and modifiers.
* [Aggregations](#aggregations): the functions that combine rows into one value.
* [Time Windows](#time-windows): how to write a window and where it may point.
* [Conditions and Operators](#conditions-and-operators): comparisons, text patterns, and constants.
* [Filters](#filters): entity filters, target filters, and `ASSUMING`.

### Anchor Time

The **anchor time** is the "as of" moment of a prediction.
Everything recorded at or before the anchor time is known and can be used as input.
Everything after it is the future: the target window starts right after the anchor time, and the model never sees that data as input.

![A timeline around the anchor time t. Left of t is known data, including the entity filter lookback window from 90 days before t through t. Right of t is the future, including the target window from just after t through 30 days after t.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/2d28c2cdf8c378b7b087a63da8be643281acf0d15bcdc4a72b45e949c0e18c77/_dot_dot_/images/diagrams/pql-anchor-time.svg)

The figure shows the churn query from [Every PQL Is Static or Temporal](#every-pql-is-static-or-temporal) at one anchor time.
The entity filter looks back; the target looks forward.
An order placed exactly at `t` counts for the filter, not for the label.

#### Anchor Times in Training

For a temporal query, Kumo creates training rows at many past anchor times.
At each one, it hides everything after the anchor, applies the entity filter, and computes the label from the window that follows.
The same customer can appear many times, once per anchor, with a different label each time.

![Training anchors spaced over history, each followed by its label window. Training anchors come first, then validation, then test. The prediction anchor is at the latest data, and its window is the unknown future.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/9a0563094db6d76a2d61b6796446fd8fe3d975d3d7fe2231310e45d211704234/_dot_dot_/images/diagrams/pql-training-anchors.svg)

The spacing above is illustrative.
Kumo chooses the number and spacing of anchors from your data and the window length; you can change them in the [training table generation plan](/sdgm/kumo-engine/user-code-guide/train-a-model/training-tables#advanced-configurations).
You need history that is much longer than the target window, so that there are enough past anchors to learn from.

#### Anchor Time at Prediction

When you generate predictions for a temporal query, you can pass `prediction_time` to set the anchor time.
If you leave it out, Kumo uses the latest timestamp found in the target's tables.
Static queries have no anchor time at prediction.
Pass a `datetime` or `pandas.Timestamp` without a timezone; Kumo reads it as UTC, and a value with no time part means midnight at the start of that day.

#### Avoid Data Leakage

> **Warning**
>
> Anchor times only protect you if your timestamps are honest.
> Leakage happens when an input contains information that would not be available at the anchor time in production.
> Common causes: a column that is filled in after the outcome is known, a timestamp that marks the start of a period whose values were summarized at its end, and an entity column that is overwritten over time.
> Fix the data or remove the column; tuning the model will not fix it.

### Supported Clauses

Every clause a query can use, followed by the rules each one must follow.

| Clause                        | Required | What it does                                                                                                   | Example                                                              |
| ----------------------------- | -------- | -------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------- |
| `PREDICT <target>`            | Required | The value to predict for each entity: a column, an aggregation, or a true or false condition.                  | `PREDICT SUM(orders.amount, 0, 30, days)`                            |
| `RANK TOP k`                  | Optional | Predict a ranked list of up to `k` items or labels. Required when the target is a list and you want a ranking. | `RANK TOP 10`                                                        |
| `CLASSIFY [TOP k]`            | Optional | Predict a set of labels, each as yes or no. Used with list targets.                                            | `CLASSIFY`                                                           |
| `FORECAST n TIMEFRAMES`       | Optional | Predict the target for `n` consecutive future windows instead of one.                                          | `FORECAST 4 TIMEFRAMES`                                              |
| `FOR EACH <table.pk>`         | Required | The entity. Must be the primary key of its table.                                                              | `FOR EACH customers.customer_id`                                     |
| `WHERE` after `FOR EACH`      | Optional | Entity filter: which entities get a prediction.                                                                | `WHERE customers.country = 'US'`                                     |
| `WHERE` inside an aggregation | Optional | Target filter: which rows count toward the target.                                                             | `COUNT(orders.* WHERE orders.amount > 100, 0, 30, days)`             |
| `ASSUMING <condition>`        | Optional | Train only on situations where a future condition held. Temporal queries only.                                 | `ASSUMING COUNT(orders.* WHERE orders.discount > 0, 0, 7, days) > 0` |

* `FOR EACH` takes the primary key of the table whose rows you want predictions for. Writing `FOR` alone, or naming a column that is not the primary key, is an error.
* A target column must be on the entity table or on a table the entity table points to directly through a foreign key, so that each entity has exactly one value. `PREDICT products.category FOR EACH orders.order_id` works because each order has one product. `PREDICT orders.amount FOR EACH customers.customer_id` fails because a customer has many orders; aggregate them instead.
* `RANK TOP k` and `CLASSIFY TOP k`: `k` must be at least 1.
* `CLASSIFY` on `LIST_DISTINCT` needs a time window. A static list target uses `RANK TOP k`, or a multicategorical column with `CLASSIFY`.
* `FORECAST n TIMEFRAMES`: `n` is from 1 to 10,000, and the target must be a numerical aggregation with a time window.
* `LIST_DISTINCT` of a foreign key that points back to the entity table has nothing to predict: `LIST_DISTINCT(orders.customer_id, 0, 30, days) FOR EACH customers.customer_id` is an error.

### Aggregations

An aggregation combines the rows linked to each entity into one value.
The aggregated table must have a foreign key directly to the entity table.
In a target, an aggregation looks forward over a time window.

| Function         | Works on                                                      | Returns                                           | Example target                                       |
| ---------------- | ------------------------------------------------------------- | ------------------------------------------------- | ---------------------------------------------------- |
| `COUNT`          | Any rows (`table.*`) or a column                              | Number of rows                                    | `COUNT(orders.*, 0, 30, days)`                       |
| `COUNT_DISTINCT` | A column that is not a key                                    | Number of distinct values                         | `COUNT_DISTINCT(orders.payment_method, 0, 90, days)` |
| `SUM`            | A numerical column                                            | Total                                             | `SUM(orders.amount, 0, 30, days)`                    |
| `AVG`            | A numerical column                                            | Mean                                              | `AVG(orders.amount, 0, 30, days)`                    |
| `MIN`, `MAX`     | A numerical column; text or timestamp columns in filters only | Smallest or largest value                         | `MAX(orders.amount, 0, 30, days)`                    |
| `FIRST`, `LAST`  | Any column except a foreign key                               | Value of the earliest or latest row in the window | `FIRST(orders.payment_method, 0, 30, days)`          |
| `LIST_DISTINCT`  | A categorical, multicategorical, or foreign key column        | The set of distinct values                        | `LIST_DISTINCT(orders.product_id, 0, 30, days)`      |

* `SUM` and `AVG` need integer or decimal columns. To total a boolean column, count the matching rows instead: `COUNT(orders.* WHERE orders.is_fraud = true, 0, 30, days)`.
* `SUM`, `AVG`, and `COUNT_DISTINCT` cannot be applied to ID, primary key, or foreign key columns.
* A target cannot be a text or timestamp value, so `MIN`, `MAX`, `FIRST`, and `LAST` of those columns only work in filters.
* `LIST_DISTINCT` returns a set. As a target, rank it with `RANK TOP k` or classify it with `CLASSIFY`; in a filter, test it with `CONTAINS`.
* Function names use an underscore (`COUNT_DISTINCT`, `LIST_DISTINCT`), not a space.

### Time Windows

An aggregation's time window is written as three extra arguments: a start offset, an end offset, and a unit.

![The aggregation call SUM(orders.amount, 0, 30, days) with labels on the function, the column, the start offset, the end offset, and the unit.](/sdgm/_fern-files/nvidia-sdgm.docs.buildwithfern.com/c86d81e1d6940fe911dfee3e39b32a70b0ec6bf1cf700d35ce56400bd48bec67/_dot_dot_/images/diagrams/pql-aggregation-window.svg)

Offsets are whole numbers measured from the [anchor time](#anchor-time).
Positive numbers look forward; negative numbers look back.

* **Boundaries:** the start is excluded and the end is included. `(0, 30, days)` covers everything after the anchor time, up to and including 30 days later.
* **Order:** the start must be smaller than the end.
* **Units:** `minutes`, `hours`, `days`, or `months`. If you leave the unit out, Kumo uses `days`. There is no `weeks` or `years`; write `14, days` or `12, months`.
* **All history:** use `-INF` as the start to look back over the full history, as in `COUNT(orders.*, -INF, 0, days)`.

Where a window may point depends on the clause:

| Clause                               | Allowed direction                         | Example                             |
| ------------------------------------ | ----------------------------------------- | ----------------------------------- |
| Target (`PREDICT`)                   | Future only. Start and end at least 0.    | `COUNT(orders.*, 0, 30, days)`      |
| Entity filter (`FOR EACH ... WHERE`) | Past only. End at most 0. `-INF` allowed. | `COUNT(orders.*, -90, 0, days) > 0` |
| `ASSUMING`                           | Future only. Start and end at least 0.    | `COUNT(orders.*, 0, 7, days) > 0`   |

A window does not have to start at the anchor time.
This target sums each customer's spending from day 10 to day 30 after the anchor time:

**`PQL`**

```sql PQL
PREDICT SUM(orders.amount, 10, 30, days)
FOR EACH customers.customer_id
```

If the anchor time is `2025-01-01 00:00:00`, the label counts orders with `2025-01-11 00:00:00 < order_time <= 2025-01-31 00:00:00`.

### Conditions and Operators

Conditions compare a column or aggregation with a constant.
You can use them in the target (to make a yes or no question) and in filters.

| Operator                   | Meaning                                                                                                         | Example                                          |
| -------------------------- | --------------------------------------------------------------------------------------------------------------- | ------------------------------------------------ |
| `=` `!=` `<` `<=` `>` `>=` | Compare values                                                                                                  | `SUM(orders.amount, 0, 30, days) > 500`          |
| `IN (a, b, ...)`           | Matches any listed value (two or more values)                                                                   | `customers.loyalty_tier IN ('gold', 'platinum')` |
| `LIKE`, `NOT LIKE`         | Text pattern with `%` at the start, the end, or both (a `%` elsewhere is not a wildcard); `NOT LIKE` needs both | `customers.email LIKE '%@example.com'`           |
| `STARTS WITH`, `ENDS WITH` | Text prefix or suffix                                                                                           | `customers.email ENDS WITH '@example.com'`       |
| `CONTAINS`, `NOT CONTAINS` | A list or multicategorical value includes an item                                                               | `products.tags CONTAINS 'organic'`               |
| `IS NULL`, `IS NOT NULL`   | Value is missing or present                                                                                     | `customers.email IS NOT NULL`                    |
| `AND`, `OR`, `NOT`, `( )`  | Combine conditions                                                                                              | `NOT customers.country IN ('US', 'CA')`          |

| Constant      | How to write it                                            | Example                                        |
| ------------- | ---------------------------------------------------------- | ---------------------------------------------- |
| Text          | Single or double quotes                                    | `'card'`, `"US"`                               |
| Number        | Integer or decimal                                         | `100`, `99.5`, `1e3`                           |
| Boolean       | `true` or `false`                                          | `orders.is_fraud = true`                       |
| Missing value | `NULL`, only with `IS`, `IS NOT`, `=`, or `!=`             | `customers.loyalty_tier IS NULL`               |
| Date or time  | No quotes: `YYYY-MM-DD`, optionally followed by `HH:MM:SS` | `customers.signup_time >= 2024-01-01 08:30:00` |
| List          | Two or more constants in parentheses, without `NULL`       | `(1, 2)`, `('US', 'CA')`                       |

### Filters

A condition can appear in three places, and each place does something different:

| Filter            | Where                                                | What it selects                                   |
| ----------------- | ---------------------------------------------------- | ------------------------------------------------- |
| **Entity filter** | After `FOR EACH ... WHERE`                           | Which entities get a training row or a prediction |
| **Target filter** | Inside an aggregation: `AGG(table.* WHERE ..., ...)` | Which rows count toward the label                 |
| **Assumption**    | After `ASSUMING`                                     | Which historical situations are used for training |

#### Entity Filter

Predict whether gold or platinum customers will spend more than \$500 next month:

**`PQL`**

```sql PQL
PREDICT SUM(orders.amount, 0, 30, days) > 500
FOR EACH customers.customer_id
WHERE customers.loyalty_tier IN ('gold', 'platinum')
```

An entity filter can use columns of the entity table, columns of tables it points to, and aggregations that look back in time.
It cannot compare a column of a table with many rows per entity directly: `WHERE orders.amount > 100` fails because a customer has many orders.
Wrap it in a count instead: `WHERE COUNT(orders.* WHERE orders.amount > 100, -INF, 0, days) > 0`.

#### Target Filter

Predict whether each customer will place an order over \$100 in the next 30 days:

**`PQL`**

```sql PQL
PREDICT COUNT(orders.* WHERE orders.amount > 100, 0, 30, days) > 0
FOR EACH customers.customer_id
```

If a target filter only mentions the entity table, Kumo warns you to move it into the entity filter, where it belongs.

## Next Steps

#### [Training Tables](/sdgm/kumo-engine/user-code-guide/train-a-model/training-tables)

Turn your query into labeled training rows and choose how they are split.

#### [Training](/sdgm/kumo-engine/user-code-guide/train-a-model/training)

Train a model on the training table and read its metrics.

#### [Prediction Guide](/sdgm/kumo-engine/user-code-guide/making-predictions)

Score entities at a prediction time and export the results.

#### [Predictive Query Examples](/sdgm/kumo-engine/get-started-with-kumo-engine/workflows-and-solutions/predictive-query-examples)

See complete queries for churn, recommendations, and forecasting on real datasets.