Only thing cooler than CTEs are Recursive CTEs, but I struggle to find use cases where I can sneak one in.

Subqueries in Subqueries in Subqueries

CTEs are so helpful for me. They make complex queries much easier to construct and lets me ‘unit test’ the parts I’m working on.

Yes, CTEs are awesome. Especially when you don't force materialization and the optimizer can work its magic.

I've had a lot of fun with window functions as well.

I was skeptical of CTEs for a long time. I just used subqueries when I could in T SQL, and then I got a new job and my new company used Postgres. In the adaptation process I took a new look at CTEs and became a convert - it's just nicer and easier to read the intermediate step than as a subquery

You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.

Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.

Example

CREATE VIEW AS const.OrderType
SELECT 
CAST (1 as 'New'),
CAST (2 as 'Pending'),
CAST (3 as 'Shipped')
GO

-- Assuming a table with OrderId and OrderTypeId

SELECT         o.OrderId 
FROM           dbo.Orders AS o
CROSS JOIN const.OrderType AS ot
WHERE         o.OrderTypeId = ot.[Pending]

-- Only returns orders where TypeId = 2

https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression

CTEs can be useful, particularly in PostgreSQL, where there are writable CTEs, but a lot of the time, I prefer using temp tables over CTEs, as they often perform better for larger datasets. I think one of my favorite constructs is window functions. I've found many uses for them, over the years.