A classical example is graph reachability. You'd express reachability via WITH RECURSIVE stating that nodes are reachable if there is a path of arbitrary length between them. (Recursion is needed since a plain SQL query can only query for paths up to a fixed length).