Level 2 · Save a summary as a table

When the same expensive query feeds several reports, save its result once and query the saved copy. CREATE TABLE ... AS SELECT, often shortened to CTAS, does exactly that.

CREATE TABLE AS SELECT

CREATE TABLE category_sales_2025 AS
SELECT p.category,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(i.quantity) AS units,
       ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount_pct / 100.0)), 2) AS net_revenue
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.status = 'completed' AND i.quantity > 0
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY p.category;

There is no column list: the new table takes its column names from the query’s aliases and its types from the expressions. That is why good aliases matter here. An unnamed SUM(...) would become a column called something like sum(i.quantity), which is painful to type in every later query.

Query the saved table

The result is an ordinary table with six rows, one per category. Later queries read it like any other:

SELECT category, orders, units, net_revenue,
       ROUND(net_revenue / orders, 2) AS revenue_per_order
FROM category_sales_2025
ORDER BY net_revenue DESC;

Furniture tops 2025 with $573,909.05 from 560 orders, so a Furniture order brings in about $1,024.84 of Furniture revenue. Decor appears in the most orders (2,155) but at $134.82 per order. Lighting sits in the middle with $385,144.61 from 1,221 orders.

A saved table is a snapshot. If a refund is recorded tomorrow, category_sales_2025 does not change until you rebuild it. A view (CREATE VIEW name AS SELECT ...) is the alternative: it stores the query, not the rows, so it is always current but recomputes every time you read it. Analysts use tables for daily reporting snapshots and views for logic that must always be live.

Staging tables in practice

Real pipelines chain these: a staging table with the cleaned order lines, a summary table built from it, and a dashboard that reads the summary. Each step is small, named and checkable on its own, the same idea as a chain of CTEs, except the intermediate results persist.

In this game every run is rolled back, so the table exists only for the statements in that run. That is why the solution creates it and queries it in one go.

Common mistakes

• Missing the semicolon between the two statements. The database then sees one long, broken statement.
• Leaving aggregates without aliases, so the new columns get awkward generated names.
• Running CREATE TABLE twice on a real database: the second time fails because the table already exists. Use CREATE OR REPLACE TABLE (DuckDB, Snowflake, BigQuery) or drop it first.
• Forgetting that the table is a snapshot and presenting stale numbers.

In SQL Server

T-SQL has no CREATE TABLE ... AS SELECT. You write SELECT ... INTO, which creates the table from the query:

SELECT Category, COUNT(*) AS Orders
INTO dbo.CategorySales2025
FROM ...;

Postgres, DuckDB, Snowflake and BigQuery use CREATE TABLE ... AS; MySQL accepts it too.