How to have temporary data:
1 - Common Table Expression (or basically a named subquery)
2 - View: works like a CTE, but you can reference it by name and not repeat the subquery every time. Views are computed on the fly, similar to CTEs.
3 - A temporary table is like a real table: it stores data on disk, and you can build indexes. But it exists only while the database connection is open.

PostgreSQL and others have materialized views, which store data on disk

When to use temporary tables ?

Temporary tables are great for experimenting when you’re just getting to know the data.