Because you access CTEs in the same way as a regular table, you can use it many times in your query. This can help if you want to use this subquery many times. For example if you want to find:

Which colours you have more bricks of than the minimum needed
The average number of bricks you have of each colour

You need to group the bricks by colour. Then filter the colours table where this count is greater than the minimum_bricks_needed for that colour. And compute the mean of the counts.

You can do the filtering with a nested subquery. And show the average in a scalar subquery. This looks like
#Oracle
#Reusable_Subqueries

Note that "group by colour" appears twice in the statement. This creates maintenance problems. If you need to change this, say to join bricks to another table, you have to do this in two places.

Using CTEs, you can do the group by once. Then refer to it in your select
#Oracle
#Reusable_Subqueries_2