Use case

#29693
A table has child -> parent relationship but does not have "top_ancestor" column.
We want to calculate it on the fly.

Describe the solution you'd like

A window function treeTopAncestor(child, parent) collects a tree in memory for every window and then outputs the most reachable ancestor for every row.