I mostly use CTEs for organization these days, and in rare cases to express queries which cannot be written without them.
These days I often write queries like this (especially when doing exploratory ad hoc queries, but also in apps) even when it's not necessary to use a CTE:
WITH
a AS (
SELECT ... FROM ... WHERE ... etc.
)
SELECT * FROM a
The first CTE query defines the input, and the main query just reads from it. Subsequent subqueries invoke steps on that input to group, filter, join, and so on.
This has a bunch of nice benefits. For example, it allows me to add steps incrementally, and to "comment out" a step I can simply change the next step's input to read from the preceding step. Each step can be read and understood in isolation.
I work a lot with Postgres, ClickHouse, and SQLite, and generally find that the database inlines and optimizes CTEs, and challenges mostly concern performance traps (like IN or EXISTS) that allly to non-CTE situations as well.
These days I often write queries like this (especially when doing exploratory ad hoc queries, but also in apps) even when it's not necessary to use a CTE:
The first CTE query defines the input, and the main query just reads from it. Subsequent subqueries invoke steps on that input to group, filter, join, and so on.This has a bunch of nice benefits. For example, it allows me to add steps incrementally, and to "comment out" a step I can simply change the next step's input to read from the preceding step. Each step can be read and understood in isolation.
I work a lot with Postgres, ClickHouse, and SQLite, and generally find that the database inlines and optimizes CTEs, and challenges mostly concern performance traps (like IN or EXISTS) that allly to non-CTE situations as well.