SQL
CTE Materialization
DuckDB can employ CTE materialization, i.e., inlining CTEs into the main query.
This is performed using heuristics: if the CTE performs a grouped aggregation and is queried more than once, it is materialized.
Materialization can be explicitly activated by defining the CTE using `AS MATERIALIZED` and disabled by using `AS NOT MATERIALIZED`.  
Take the following query for example, which invokes the same CTE three times:  
```sql
WITH t(x) AS (⟨complex_query⟩)
SELECT *
FROM
t AS t1,
t AS t2,
t AS t3;
```  
Inlining duplicates the definition of `t` for each reference which results in the following query:  
```sql
SELECT *
FROM
(⟨complex_query⟩) AS t1(x),
(⟨complex_query⟩) AS t2(x),
(⟨complex_query⟩) AS t3(x);
```  
If `⟨complex_query⟩` is expensive, materializing it with the `MATERIALIZED` keyword can improve performance. In this case, `⟨complex_query⟩` is evaluated only once.  
```sql
WITH t(x) AS MATERIALIZED (⟨complex_query⟩)
SELECT *
FROM
t AS t1,
t AS t2,
t AS t3;
```  
If one wants to disable materialization, use `NOT MATERIALIZED`:  
```sql
WITH t(x) AS NOT MATERIALIZED (⟨complex_query⟩)
SELECT *
FROM
t AS t1,
t AS t2,
t AS t3;
```