Temp Tables and CREATE TABLE AS SELECT in SQL
`CREATE TABLE AS SELECT` and `CREATE TEMP TABLE` materialize the result of a query into an actual database table. Unlike a CTE, the result persists beyond the query that creates it and can be referenced by subsequent queries in the same session.
Before this Common Table Expressions (CTEs)
CREATE TEMP TABLE ... AS SELECT runs a query and stores the result as a table you can reference repeatedly in the same session.
A CTE names a subquery within a single statement. The moment that statement finishes, the result is gone. A temp table is different: the result persists for the rest of your session, and you can query it as many times as you like with separate SELECT statements — without re-running the original computation each time.
You're building a multi-step analysis. Step one is an expensive aggregation across a large orders table. Steps two and three both need that aggregated data. With a CTE, you'd have to include the aggregation inside every query that needs it. With a temp table, you run it once and store the result:
CREATE TEMP TABLE monthly_revenue AS
SELECT
DATE_TRUNC('month', created_at) AS month,
status,
SUM(total_amount) AS revenue
FROM orders
GROUP BY 1, 2;Now monthly_revenue is a real table in your session. Query it as many times as you need:
SELECT status, AVG(revenue) FROM monthly_revenue GROUP BY status;
SELECT month, SUM(revenue) FROM monthly_revenue GROUP BY month;The aggregation ran once. Both queries read from the stored result.
When your session ends, PostgreSQL drops the temp table automatically. You don't need to clean it up. And the table is invisible to other sessions — even if two analysts create a temp table with the same name, each gets a private copy in their own session. There is no naming conflict.
You can also add indexes to a temp table after creating it, which a CTE cannot have. For large intermediate results that get queried repeatedly with WHERE filters, an index can meaningfully speed up the downstream queries.
When should you use a CTE instead? When the intermediate result is only needed once, inside a single query. When you need it across multiple queries or statements in the same session, a temp table is the right tool.
The one thing that trips people up: a temp table created inside a transaction is dropped if the transaction rolls back.
If your session runs BEGIN, creates a temp table, then hits an error that triggers a ROLLBACK, the temp table disappears along with everything else in that transaction. If you're building a multi-step pipeline with temp tables, be aware of your transaction boundaries.
You create a temp table in one session. Can a second concurrent session query that same temp table?
9 Temp Tables and CREATE TABLE AS SELECT practice problems
Write a query to return the status, order count, and total amount for each `status` value.
Write a query to return the category ID, product count, and average price for each `category_id` value.
Write a query to return the department ID, employee count, and earliest hire date for each `department_id` value.
Write a query to return each high-activity customer's ID and order count.
Write a query to return each qualifying customer's ID, order count, and total spend.
Write a query to return each customer's ID, order count, total spend, and the average individual order amount across every order.
Write a query to return the status and order count for each high-volume status.
Write a query to return each qualifying category's ID, product count, combined price, and average product price.
Write a query to return each qualifying customer's ID and total spend.
These problems are part of the Temp Tables and CREATE TABLE AS SELECT lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.