Lesson 10 of 12 · 25 min
Recursive CTEs: hierarchies, chains and date spines
By the end of this lesson you can write a recursive CTE with an anchor, a recursive step and a termination condition, use it to walk a hierarchy up or down, and generate a gap-free date series.
Wadi Market's categories table is a tree: parent_category_id points to the parent row, and it is NULL for the four departments. Headphones sits under Audio, which sits under Electronics. A self-join climbs one level; climbing a tree of any depth needs a query that repeats itself until there is nothing left to climb. That is a recursive CTE.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons