Skip to main content

​
WHERE <Aggregation_Function>(<table>.<column_name>, <start>, <end>) <comparison_operator> <constant> (Optional)

​
Description

The WHERE clause filters data before running predictions, allowing you to exclude irrelevant entities or targets from aggregation. Filters can be static (based on direct column values) or temporal (using aggregations over time).

​
Static Filters

A static filter does not involve aggregations and applies direct conditions to table columns.

​
Example 1: Basic Static Filter

PQL
WHERE <entity_table>.<column_name> <comparison_operator> <constant>
WHERE user.country = 'US'

​
Example 2: Using a Static Filter in an Aggregation

PQL
PREDICT COUNT(transaction.* WHERE transaction.amount > 100)
FOR EACH user.user_id WHERE user.country = 'US'

​
Example 3: One-Hop Filters (Connected Tables)

PQL
WHERE region.num_inhabitants < 10000
Note: A unique foreign key must link user to region to ensure each user belongs to a single region.

​
Example 4: Combining Multiple Filters

PQL
WHERE (user.country = 'US' OR region.num_inhabitants < 10000)  
      AND user.dietary = 'Vegetarian'

​
Temporal Filters

Temporal filters apply conditions based on past activity within a specified time window.

​
Example 5: Temporal Filter Usage

PQL
FOR EACH user.user_id WHERE COUNT(views.id, -30, 0) > 0  
FOR EACH user.user_id WHERE LIST_DISTINCT(purchases.item_category, -90, 0) CONTAINS 'Food'  
FOR EACH user.user_id WHERE LAST(status.status, -1, 0) = 'ACTIVE'  
FOR EACH user.user_id WHERE COUNT(transactions.*, -INF, 0) > 0

​
Inline and Nested WHERE Filters

​
Using WHERE Within an Aggregation

PQL
PREDICT COUNT(transaction.* WHERE transaction.value > 10, 0, 7)
FOR EACH user.user_id WHERE COUNT(transaction.*, -7, 0) > 0

​
Nested Temporal Filters

PQL
PREDICT COUNT(transaction.*, 0, 7)  
FOR EACH user.user_id  
WHERE COUNT(transaction.* WHERE transaction.value > 10, -7, 0) > 0

​
Multiple Target Tables

​
Example 6: Multiple Targets

PQL
PREDICT COUNT(session.*, 0, 7) > 10 OR SUM(transaction.value, 0, 5) > 100  
FOR EACH user.user_id
Invalid Example (Mixing Static & Temporal)
PQL
PREDICT COUNT(session.*, 0, 7) > 10 OR transaction.value > 2

​
Filtering by Specific Date/Time

PQL
WHERE customers.date_joined < 2022-01-01 12:45:30

​
Modifying Filters for Batch Prediction

​
Example 7: Changing Entity Filters

PQL
WHERE customers.status = 'ACTIVE' AND COUNT(transactions.*, -90, 0) > 0

​
Modifying Target Filters at Batch Prediction Time

​
Example 8: Adjusting Target Conditions

PQL
PREDICT LIST_DISTINCT(transactions.article_id  
                      WHERE articles.product_type_name = 'Trousers'  
                            AND transactions.price >= 50,  
                      0, 90, days)  
FOR EACH user.user_id
PQL
WHERE articles.product_type_name = 'Trousers' AND articles.color = 'blue'

​
Summary

  • WHERE filters exclude irrelevant entities or targets before aggregation.
  • Static filters apply direct column conditions.
  • Temporal filters use past activity to refine predictions.
  • You can combine multiple filters with AND/OR/NOT.
  • One-hop filters allow filtering based on connected tables (requires a unique foreign key).
  • Batch prediction filters let you modify conditions at inference without retraining, but must align with training distribution.
⌘I