It is convenient to use CTE in the following cases:
- When one request can get data, and its size fits in memory space
- Multiple uses of the results of this query are required
- Creating recursive queries
A bonus would be the improved readability of your SQL query.