[eigenrick]

To my untrained eyes, SQL did not appear to have things like windowing, hierarchical relationships, (trees, graphs) in mind when initially designing the language.

Support for these things have been wedged into the language by numerous additions, and I think the fundamental syntactical framework of SQL is cracking under the pressure. Granted, it's been a while since I've had to traverse a tree using SQL, but I do recall that the query was very painful to write, and even more painful to execute efficiently.

Also, these things only work if you've wedged your data model into rows and columns. (or you use non-standard language extensions like jsonb)

PRQL, on the other hand, rearranges the syntactical structure to be more extensible, and also agnostic over the underlying shape of the data model. It works the same regardless of whether you're using an RDBMS or a graph/document database.

Is the result more complicated? IMO, only in the simplest examples. PRQL shines vs SQL when you have multiple stages of aggregation, like pre and post aggregation filtering, or referencing synthesized data, etc.

[ttfkam]

  WITH RECURSIVE

is how graph/tree queries work in modern SQL (assuming you aren't using something like ltree [0]. The first part of the recursive CTE identifies the root record and then UNION ALL with the subsequent items referencing what came before.

Is it as easy as a Cypher query in Neo4j? Not all all. But it is arguably more flexible in that you can represent far more data structures and link to non-graph data easily.

I just unwrapped nested JSON columns into a flat key-value set with it last week. It's deceptively powerful.

[0] https://www.postgresql.org/docs/current/ltree.html