27: Recursive Common Table Expressions

An example:

WITH RECURSIVE countdown(val) AS (
    SELECT 3 as val
    UNION
    SELECT val - 1 FROM countdown WHERE val > 0
)
SELECT *
FROM countdown;
--- returns values 3, 2, 1
copy

Recursive CTEs Step by Step

Why would we want to use this?

WITH RECURSIVE countdown(val) AS (
    SELECT 3 as val -- Initial, non-recursive query
    UNION
    SELECT val - 1 FROM countdown WHERE val > 0 -- recursive query
)
SELECT *
FROM countdown;
--- returns values 3, 2, 1
copy

The steps that this runs by:

Why use Recursive CTEs?

A more practical in our use case would be things like "Instagram suggestions".

WITH RECURSIVE suggetions(leader_id, follower_id, depth) AS (
        SELECT leader_id, follower_id, 1 AS depth
        FROM followers
        WHERE follower_id = 1000
    UNION
        SELECT followers.leader_id, followers.follower_id, depth + 1
        FROM followers
        JOIN suggetions ON followers.leader_id = suggetions.follower_id
        WHERE depth < 3
)
SELECT DISTINCT users.id, users.username
FROM suggestions
JOIN users ON users.id = suggestions.leader_id
WHERE depth > 1
LIMIT 5;
copy