One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
An example of self referencing is recursion: Recursive Queries Using CTE
For exciting Microsoft definitions Taken from Books Online:
A CTE can be used to:
- Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
- Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
- Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
- Reference the resulting table multiple times in the same statement.