Recursive Common Table Expressions (CTE) are hard to understand until you've worked through at least one real-world example. The SQLite documentation on the WIT

Recursive Common Table Expressions (CTE) are hard to understand until you've worked through at least one real-world example. The SQLite documentation on the WITH Clause has a couple of simple examples [1] for an Org Chart and a Family; both represent hierarchical data structures. The Fossil discussion involves the SCM's representation of a File System, probably the most common hierarchical data structure we regularly encounter. Hierarchical data structures are notoriously hard in SQL. Recursive CTEs are not easy but they are useful and efficient for hierarchical data. [1] https://www.sqlite.org/draft/lang_with.html#hierarchical_query_examples https://www.sqlite.org/draft/lang_with.html#hierarchical_que...