CTEs come in particularly handy for hierarchical views (or table-valued functions if you prefer). I've been working on one app lately part of which allows supervisors to review data entry made by their staff and department heads can see and review everyone in their department, all from the same code just by passing in the id of the person accessing the data. Our organization is small enough performance isn't impacted very much.

Obviously you have to be careful about just how deep your hierarchy goes, etc., but for smallish hierarchical data they are quite nice.