Hierarchical data — org charts, comment threads, file trees — looks unfriendly in SQL until you discover recursive CTEs. One query, no application loops, no N+1. Here is the pattern, the four common shapes, and the performance considerations that decide whether it scales.
Most developers learn SQL aggregations, hit a wall, and pull data into the application to compute running totals or per-group ranks. Window functions are the SQL feature that replaces that round trip with one query — and most teams use less than 10% of what they can do.