Recursive queries and macros

DuckDB supports recursive queries, which are useful for working with hierarchical data, such as organizational charts, product categories, and family trees.

Let’s look at how to traverse the hierarchy of wine types and retrieve information about our wine varieties efficiently.

Wine classification hierarchy

Let’s start with a table of wine with the association between the wine variety hierarchy expressed as a subclass column:

Figure 10.17 – Simplified wine hierarchy

We can create and load the wines table like this:

CREATE OR REPLACE TABLE wines AS
SELECT *
FROM read_csv('wines.csv');

Now, let’s look at the contents of the wines table:

SELECT *
FROM wines
ORDER BY wine_id;

This query returns a small collection of wines and wine varieties:

┌─────────┬─────&...