Nice work! Just as you can have multiple columns in your CTE, you can also have multiple CTEs in one query, some of which may be recursive. The rule for syntax is as follows: if at least one CTE is recursive, start the whole query with
WITH RECURSIVE and then define your CTEs just as you would normally do. Take a look at the example below.
WITH RECURSIVE prime(x) AS (
total(multiply,prime,score) AS (
x * (multiply+1)
FROM prime, total
WHERE multiply < 3
ORDER BY multiply, prime;
In this example, we first create a list of the first 5 prime numbers, then we create a recursive CTE that calculates the multiples of these prime numbers. Note that the word
RECURSIVE is always used before the first CTE, even if that CTE is not recursive.