Oracle subquery using WITH clause is a popular choice among the developers. From Oracle 12C it's possible to define PLSQL function inside WITH clause. It's a handy feature which is really useful when making complex queries