Aren't most of your examples just syntactic sugar that could be done with a subquery?

I would prefer to use simpler constructs if possible for ease of future maintenance.

From what I can see, there are only two situations where a Common Table Expression is really useful:

1) Where you need to join a subquery to itself, so you would otherwise need to either duplicate the subquery or use a temp table.

2) Where you want to use recursion.

Can anyone thing of any others?