Recursive CTE can be used for this kind of queries. Or you can use nested sets / intervals depending on your workload and the tradeoffs you're ready to make.