> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Dynamic filters

> Dynamic filters function as filter operators, but the filter condition contains a dynamic variable. They enable filtering data streams in real-time and allow a condition to be defined that incoming data must meet in order to be processed.

An valid dynamic filter comprises the following components:

* A comparison operator including `<`, `>`, `<=`, `>=` and `BETWEEN`
* A column as the left side
* A scalar subquery as the right side

Additionally, the dynamic filter condition must not be part of an `OR` expression. For example, `v > (select max(v) from t2) OR a > 0` is invalid.

The following query returns the name of all products whose profit margin is greater than the maximum profit margin recorded in the `sales` table.

```sql theme={null}
WITH max_profit AS (SELECT max(profit_margin) max FROM sales)
SELECT product_name FROM products, max_profit
WHERE product_profit > max;
```

The dynamic filter in this query is in the `WHERE` clause. The filter condition `product_profit > max` compares the `product_profit` column from the `products` table to the maximum value of the `profit_margin` column from the `sales` table, which is stored in the subquery `max_profit`. The value of the maximum profit margin is dynamic and changes based on the values in the `sales` table.

The following query calculates the parts that cost more than 0.01% of the total money spent.

```sql theme={null}
CREATE MATERIALIZED VIEW mv1 AS
SELECT
  ps_partkey,
  sum(ps_supplycost * ps_availqty) AS value
FROM
  partsupp
GROUP BY
  ps_partkey
HAVING
  sum(ps_supplycost * ps_availqty) > (
    SELECT
      sum(ps_supplycost * ps_availqty) * 0.0001
    FROM
      partsupp
  )
```

Under the hood, RisingWave maintains this materialized view with a continuous streaming job that filters the aggregation results according to the `HAVING` clause. Note that the subquery result, which is 0.01% of the total cost, is constantly changing, either increasing or decreasing, depending on the incoming changes to the `partsupp` table.

As this value increases, more results will satisfy this condition and be output; conversely, as this value decreases, more rows are filtered out, and fewer results will be output.

RisingWave implements this using dynamic filters, internal stateful operators that keep a full set of results before filtering and decides which changes should be output.